Welcome to iPadForums.net Guest - Click Here to Register

Numbers formula help

This is a discussion on Numbers formula help within the iWork Forum forums, part of the iPad App Store category; 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 ...

iPadForums.net is the original Apple iPad Forum! Registered Users do not see these ads. Please Register - It's Free!
+ Reply to Thread
Page 1 of 2 1 2 LastLast
Results 1 to 10 of 17
  1. #1
    iPF Noob
    Member #
    156835
    Join Date
    Jan 2012
    Posts
    8

    Numbers formula help

    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.
    Numbers formula help-photo.jpg

  2. Ads


  3. #2
    iPF Legend
    Member #
    28530
    Liked
    1523 times
    Join Date
    Jan 2011
    Location
    Walla Walla, WA
    Posts
    15,431
    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.

    Numbers formula help-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)

  4. #3
    iPF Noob
    Member #
    156835
    Join Date
    Jan 2012
    Posts
    8
    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
    Last edited by Abunaixd; 01-25-2012 at 10:45 PM.

  5. #4
    iPF Legend
    Member #
    28530
    Liked
    1523 times
    Join Date
    Jan 2011
    Location
    Walla Walla, WA
    Posts
    15,431
    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.

  6. #5
    iPF Noob
    Member #
    156835
    Join Date
    Jan 2012
    Posts
    8
    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.

  7. #6
    iPF Legend
    Member #
    28530
    Liked
    1523 times
    Join Date
    Jan 2011
    Location
    Walla Walla, WA
    Posts
    15,431
    Made the mistake of opening it up, just to take a look. Oh well, I can sleep in tomorrow, more than usual.

    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.

    Numbers formula help-savingsformula.jpg

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

  8. #7
    iPF Legend
    Member #
    28530
    Liked
    1523 times
    Join Date
    Jan 2011
    Location
    Walla Walla, WA
    Posts
    15,431
    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.

  9. #8
    iPF Noob
    Member #
    156835
    Join Date
    Jan 2012
    Posts
    8
    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.

  10. #9
    iPF Legend
    Member #
    28530
    Liked
    1523 times
    Join Date
    Jan 2011
    Location
    Walla Walla, WA
    Posts
    15,431
    No problem. It was a fun challenge.

  11. #10
    iPF Noob
    Member #
    156835
    Join Date
    Jan 2012
    Posts
    8
    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.


Links

Posting Permissions

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

Similar Threads

  1. iPad Numbers formula help
    By Zera in forum iWork Forum
    Replies: 12
    Last Post: 09-02-2011, 08:49 PM
  2. Help with Numbers formula
    By Jmfocus in forum iWork Forum
    Replies: 30
    Last Post: 08-30-2011, 09:06 PM
  3. Numbers help with formula running down a page
    By BoredFatMan in forum iWork Forum
    Replies: 3
    Last Post: 05-11-2011, 01:19 PM
  4. Numbers Formula Instructions
    By Hal05154 in forum iWork Forum
    Replies: 3
    Last Post: 01-23-2011, 03:37 PM
  5. Formula One Car Crash Video
    By Gabi in forum Off-Topic
    Replies: 3
    Last Post: 09-21-2010, 02:04 AM

Search tags for this page

apple numbers formula for countdown
,
formula help numbers
,
iwork check list
,
iworks logical date conditions
,
match two criteria iwork
,
numbers
,

numbers formula help

,
printable spending log
Click on a term to search for related topics.