Results 1 to 14 of 14
  1. #1
    Uranium Lounger viking33's Avatar
    Join Date
    Jun 2002
    Location
    Cape Cod, Massachusetts, USA
    Posts
    6,308
    Thanks
    0
    Thanked 1 Time in 1 Post

    Works SS formula

    I've been using this formula in many of my spreadsheets, since DOS, Quattro now Works and Excel.

    =IF(F32+G32=(I32:Y32),1.0

    I use this in a sheet for disbursements where F32 and G32 are the totals of check and cash payments.

    I32 thru Y32 are the totals of the detailed catagories of these payments. So that if I have a check for $100.00 it will show in column F32. Of this amount possible 60.00 will be for Computer Programs and 40.00 for office supplies, totalling $100.00. They may be be columns I32 and M32.
    If these entries are inputted correctly then the above formula will show a 1 as being correct, 0 if not so.
    This is great to verify that the correct numbers are "poked in", but if a large number of entries are made and one is INcorrect, it will NOT show where the error is, just that there was an error made somewhere in the new data input.

    To avoid having to go back through all the entries made, is there a way to have this error "flagged" or highlighted or some way show exactly what line or column contains the incorrect numbers?

    I'm not sure if I have explained this clearly but it's a start.
    BOB
    http://lounge.windowssecrets.com/S/flags/USA.gif http://lounge.windowssecrets.com/S/f...sachusetts.gif


    Long ago, there was a time when men cursed and beat on the ground with sticks. It was called witchcraft.
    Today it is called golf!

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Works SS formula

    Did you mean

    =IF(F32+G32=SUM(I32:Y32),1,0)

    or something else? I don't understand the formula as posted.

  3. #3
    Uranium Lounger viking33's Avatar
    Join Date
    Jun 2002
    Location
    Cape Cod, Massachusetts, USA
    Posts
    6,308
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Works SS formula

    Hans,
    Yes, that is correct. I forgot the SUM.
    BOB
    http://lounge.windowssecrets.com/S/flags/USA.gif http://lounge.windowssecrets.com/S/f...sachusetts.gif


    Long ago, there was a time when men cursed and beat on the ground with sticks. It was called witchcraft.
    Today it is called golf!

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Works SS formula

    If you have a number of entries that don't add up to the correct amount, you can't point to any one of them and say "This one is incorrect". The only thing you know is that their sum is not correct, but you cannot tell whether this is do to one being incorrect, or two of them, or even all of them. Or do I completely misunderstand your intention?

  5. #5
    Uranium Lounger viking33's Avatar
    Join Date
    Jun 2002
    Location
    Cape Cod, Massachusetts, USA
    Posts
    6,308
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Works SS formula

    Hans,
    I guess what I had in mind was a comparison of the two totals. That is:

    If the total check was for $100.00, then the total of the itemized amounts must equal $100.00 also.
    If NOT, then the particular line item would be flagged or shown as incorrect.

    Now it will only show that some where there is an error.
    BOB
    http://lounge.windowssecrets.com/S/flags/USA.gif http://lounge.windowssecrets.com/S/f...sachusetts.gif


    Long ago, there was a time when men cursed and beat on the ground with sticks. It was called witchcraft.
    Today it is called golf!

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Works SS formula

    I'm sorry, I don't understand. Which "particular line item" do you have in mind?

  7. #7
    Uranium Lounger viking33's Avatar
    Join Date
    Jun 2002
    Location
    Cape Cod, Massachusetts, USA
    Posts
    6,308
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Works SS formula

    The line that contains the error in comparison.
    BOB
    http://lounge.windowssecrets.com/S/flags/USA.gif http://lounge.windowssecrets.com/S/f...sachusetts.gif


    Long ago, there was a time when men cursed and beat on the ground with sticks. It was called witchcraft.
    Today it is called golf!

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Works SS formula

    If Works supports custom formats the way Excel does (I don't have Works myself, so I can't test), you could set the format for the cell containing the comparison to
    <code>
    0;-0;<!t>[Red]0
    </code>
    The first section is for positive numbers, the second for negative numbers and the third for zero, so zero will be displayed in red.

  9. #9
    Uranium Lounger viking33's Avatar
    Join Date
    Jun 2002
    Location
    Cape Cod, Massachusetts, USA
    Posts
    6,308
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Works SS formula

    Guess I'm out of luck on that idea.
    I don't see any way to apply a custom format to a cell.

    Oh well, thanks for your help here, Hans. <img src=/S/disappointed.gif border=0 alt=disappointed width=15 height=15>
    BOB
    http://lounge.windowssecrets.com/S/flags/USA.gif http://lounge.windowssecrets.com/S/f...sachusetts.gif


    Long ago, there was a time when men cursed and beat on the ground with sticks. It was called witchcraft.
    Today it is called golf!

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Works SS formula

    Perhaps someone who has Works will have a suggestion. <img src=/S/crossfingers.gif border=0 alt=crossfingers width=17 height=16>

  11. #11
    Uranium Lounger viking33's Avatar
    Join Date
    Jun 2002
    Location
    Cape Cod, Massachusetts, USA
    Posts
    6,308
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Works SS formula

    I don't know, Hans!

    I sometimes think I'm only one of three people in the world who still hang on to Works.
    It sure does have it's "warts" but I've become so used to the SS included in it that I would dread the thought of setting up all new sheets.

    Maybe one of those two remaining people belong to the Lounge? <img src=/S/compute.gif border=0 alt=compute width=40 height=20> <img src=/S/laugh.gif border=0 alt=laugh width=15 height=15>
    BOB
    http://lounge.windowssecrets.com/S/flags/USA.gif http://lounge.windowssecrets.com/S/f...sachusetts.gif


    Long ago, there was a time when men cursed and beat on the ground with sticks. It was called witchcraft.
    Today it is called golf!

  12. #12
    Super Moderator
    Join Date
    Dec 2000
    Location
    Renton, Washington, USA
    Posts
    12,560
    Thanks
    0
    Thanked 4 Times in 4 Posts

    Re: Works SS formula

    I have it installed, but, use it ONLY to help others "Convert" their files to a REAL program.

    Now running HP Pavilion a6528p, with Win7 64 Bit OS.

  13. #13
    Uranium Lounger viking33's Avatar
    Join Date
    Jun 2002
    Location
    Cape Cod, Massachusetts, USA
    Posts
    6,308
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Works SS formula

    Hi Dave,
    I wonder who the other ( of the three ) is, who still has it installed?

    Can you see any way to implement HansV's post <post#=541,619>post 541,619</post#> in your version of Works?
    BOB
    http://lounge.windowssecrets.com/S/flags/USA.gif http://lounge.windowssecrets.com/S/f...sachusetts.gif


    Long ago, there was a time when men cursed and beat on the ground with sticks. It was called witchcraft.
    Today it is called golf!

  14. #14
    Super Moderator
    Join Date
    Dec 2000
    Location
    Renton, Washington, USA
    Posts
    12,560
    Thanks
    0
    Thanked 4 Times in 4 Posts

    Re: Works SS formula

    No I don't, as I use it only to convert Works files to Word, Excel and etc.

    It seems that most Works users can not follow instructions on converting their files when one changes over to Office. Some do NOT even know what file name they are using, let along where it is being saved.

    Now running HP Pavilion a6528p, with Win7 64 Bit OS.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •