Numbers formula help

Discussion in 'iWork Forum' started by Abunaixd, Jan 25, 2012.

  1. Abunaixd

    Abunaixd
    Expand Collapse
    iPF Noob

    Joined:
    Jan 25, 2012
    Messages:
    8
    Thanks Received:
    0
    Hello everyone I'm having an issue with my formula on numbers. I'm trying to create a banking log to track my spending. I want to have a check list control weather or not the total given is added to the category or not. But im unable to link the IF and SUMIF statements. Any help would be greatly appreciated.
    photo.jpg
     
  2. twerppoet

    twerppoet
    Expand Collapse
    iPad Legend

    Joined:
    Jan 8, 2011
    Messages:
    19,826
    Thanks Received:
    4,898
    Ok, I think I understand what you want to do. From what I can see it should work, but here are a couple things you can try.

    One is to be more explicit with the condition test in the IF statement. I had problems with checkmarks evaluating as TRUE before, even though the bare-bones method you used should work.

    is_true.jpg

    The other is to make a quick test with the same formula, but select ranges of cells instead of entire columns. I've noticed that some functions are unable to ignore Headers in columns, and fail when the header is included in the selection.

    You also might be able to cobble together something with the IFSUM functions instead, though it would be less elegant.

    If none of that works, I'd be happy to fiddle with the spreadsheet if you can provide a link to a copy (minus any personal data)
     
  3. Abunaixd

    Abunaixd
    Expand Collapse
    iPF Noob

    Joined:
    Jan 25, 2012
    Messages:
    8
    Thanks Received:
    0
    Thank you for your reply I've tried both ways using the IF("Transactions::B=True", as well as selecting the ranges but it still doesnt read the value. Here is the link to my spreadsheet hopefully you have better luck than I do.

    Blank.zip
     
    #3 Abunaixd, Jan 25, 2012
    Last edited: Jan 25, 2012
  4. twerppoet

    twerppoet
    Expand Collapse
    iPad Legend

    Joined:
    Jan 8, 2011
    Messages:
    19,826
    Thanks Received:
    4,898
    Wow. 4Shared really makes you jump through hoops if you aren't a member, but I got the file.

    I'll see what I can figure out; no promises. It is getting late, and with my schedule it may be this time tomorrow before I get back to you.
     
  5. Abunaixd

    Abunaixd
    Expand Collapse
    iPF Noob

    Joined:
    Jan 25, 2012
    Messages:
    8
    Thanks Received:
    0
    Glad you got it, I understand if it takes awhile so please don't feel rushed. I appreciate any help you can offer. Also I didn't realize it was such a hustle sorry about that.
     
  6. twerppoet

    twerppoet
    Expand Collapse
    iPad Legend

    Joined:
    Jan 8, 2011
    Messages:
    19,826
    Thanks Received:
    4,898
    Made the mistake of opening it up, just to take a look. Oh well, I can sleep in tomorrow, more than usual. :D

    Anyway, I got it to work.

    The mistake is using the IF function at all. IF can't test an entire column of values. I realized this when I decided to simplify the formula and test each part individually. No matter what I did I could not make IF test as true with more than one cell selected. (Though in hind site I wonder what would happen if I made all cells in the selection true. That could be a handy trick)

    Fortunately, you don't need to. The IFSUMS function can test for multiple conditions. By testing for both (Category = A8:Savings) and (Paid = True) you get the results you want. The picture should make it clearer.

    SavingsFormula.jpg

    I did not test it beyond the one entry, so no guarantees, but I think it will do exactly what you want.
     
  7. twerppoet

    twerppoet
    Expand Collapse
    iPad Legend

    Joined:
    Jan 8, 2011
    Messages:
    19,826
    Thanks Received:
    4,898
    BTW, the Paid On formula is not going to do what you want. The date will always be the current date. I have yet to find an automatic way to set a date in a cell. If you find one that works, please post it.
     
  8. Abunaixd

    Abunaixd
    Expand Collapse
    iPF Noob

    Joined:
    Jan 25, 2012
    Messages:
    8
    Thanks Received:
    0
    Thank you so much for your help twerppoet you sir are my new hero! As for the Paid On formula, I didn't realize the dates didn't stay static. I did do some research on the problem and the answers I found were to use VB coding or use circular referencing which numbers doesn't seem to support.
     
  9. twerppoet

    twerppoet
    Expand Collapse
    iPad Legend

    Joined:
    Jan 8, 2011
    Messages:
    19,826
    Thanks Received:
    4,898
    No problem. It was a fun challenge.
     
  10. Abunaixd

    Abunaixd
    Expand Collapse
    iPF Noob

    Joined:
    Jan 25, 2012
    Messages:
    8
    Thanks Received:
    0
    Well since I was unable to fix the date to make it static i decided to add a count down to give me the days till due and if the check box is checked it displays Paid.

    Incase anyone is interested I changed the "Paid On" cells to this formula : If(B2, "Paid", If(A2=0, " ", A2 - TODAY)) If the cells in A2 are left blank, C2 displays blank also. If a date is entered into A2, the countdown until that date is displayed and if the "Paid" box is checked, C2 will display Paid in its cell.
     

Share This Page



Search tags for this page
apple number - sumifs
,
apple numbers checklist current date auto
,
apple numbers formula count down
,
apple numbers formula for countdown
,
check list numbers ioad
,
if formula in numbers mac
,
numbers
,
numbers app true formula
,
numbers data entry
,

numbers formula help