Welcome to iPadForums.net Guest - Click Here to Register

Help with Numbers formula

This is a discussion on Help with Numbers formula within the iWork Forum forums, part of the iPad App Store category; Originally Posted by twerppoet Originally Posted by Jmfocus Originally Posted by twerppoet BTW, what formula are you using to automatically set the date in a ...

iPadForums.net is the original Apple iPad Forum! Registered Users do not see these ads. Please Register - It's Free!
+ Reply to Thread
Page 3 of 4 FirstFirst 1 2 3 4 LastLast
Results 21 to 30 of 31
  1. #21
    iPad Fan!
    Member #
    93425
    Join Date
    Aug 2011
    Posts
    10
    Quote Originally Posted by twerppoet View Post
    Quote Originally Posted by Jmfocus View Post
    Quote Originally Posted by twerppoet View Post
    BTW, what formula are you using to automatically set the date in a cell? I've been trying to figure out how to do this.
    I created a separate tab where I enter the daily info and it matches with the spreadsheet.
    Ok, I might be able to figure that out. Thanks.
    After I created the spreadsheet, I clicked on the + tab and created a form and matched it up to the columns on the spreadsheet and formatted the date entry and it autotfilled on the spreadsheet. It was easy and didn't require a formula.

  2. Ads


  3. #22
    iPF Legend
    Member #
    28530
    Liked
    1546 times
    Join Date
    Jan 2011
    Location
    Walla Walla, WA
    Posts
    15,470
    Quote Originally Posted by Jmfocus View Post
    After I created the spreadsheet, I clicked on the + tab and created a form and matched it up to the columns on the spreadsheet and formatted the date entry and it autotfilled on the spreadsheet. It was easy and didn't require a formula.
    Ah, a form. I haven't played with those much. I should. Thanks again.

  4. #23
    iPad Fan!
    Member #
    93425
    Join Date
    Aug 2011
    Posts
    10
    Quote Originally Posted by 88raymond View Post
    Quote Originally Posted by twerppoet
    BTW, what formula are you using to automatically set the date in a cell? I've been trying to figure out how to do this.
    use the function 'now' or 'today'... but make sure you do the copy and paste / values "thing" right after to "fix" it, or else that date/time changes everytime

    to the OP, here's something i cobbled up... Blank.numbers - 4shared.com - online file sharing and storage - download. the dates are set (set day 1 - the cell in green - to your liking), you only input the values.

    i didn't use a footer as you might want to always know what the latest values are on top, so they are all in the header...
    It looks like that formula will need to be copied and filled down the column? I see what you are doing.


    Twerppoet, you posted this formula;

    C2-LOOKUP(Max(DateColumn),DateColumn,ValueColumn)

    I types this in exactly and this is what it says;

    The operator "-" expects a number, date, or duration but found "C2."

    What am I doing wrong?

    EDIT, See post below.
    Last edited by Jmfocus; 08-30-2011 at 06:19 PM.

  5. #24
    iPad Fan!
    Member #
    93425
    Join Date
    Aug 2011
    Posts
    10
    Quote Originally Posted by twerppoet View Post
    Quote Originally Posted by Jmfocus View Post

    C2-LOOKUP(Max(DateColumn),DateColumn,ValueColumn) Edit, fixed mistake. I got the two formulas mixed up. Sorry. It was correct in the screen shot though.

    If that still does not make sense, here is the spreadsheet I've been playing with, with both the crazy and Raymond inspired simpler formulas. You can play with them until you understand what is going on.
    Do I type that in as text exactly as you have it. If so, it doesn;t do the computation, it just shows the actual text in the footer cell.
    No, you can't enter formulas that way in the iPad. It just becomes a text string. I wish you could. Once you know for sure what you want it would be a lot easier than the system used.

    Let me try a walk though. All formula's are entered this way (more or less), with tap and holds to place the cursor for operator entry or deleting optional place holders.

    You double tap on the cell to start the cell editor, then select the = sign to go into formula mode.

    Then you select the cell C2 from the table, then the - sign from the operator section of the editor. That gives you C2- This is your initial value. The results of the following functions will be subtracted from it.

    You tap the function key in the editor, go to the Reference section and select LOOKUP. This will give you the LOOKUP function with three placeholders. This is what determins where you will find your value to be subtracted from C2. It has three parts. What it is looking for, Where it is going to look, and Where it will return results from.

    Tap the first placeholder (search-for) so that it is highlighted. Open functions again, go to the Statistical section and tap on MAX. You get the MAX function with one placeholder. This is the function that looks at the entire column and returns the maximum (most recent date) as a value. It acts as what you are looking for in the LOOKUP function.

    Tap on the MAX place holder (value) so that it is highlighted, then select the column with the dates. Do this by tapping the bar above the column, where the letter designator for that column is visible. This should result in the (value) settings changing to the text that's in the header.

    Now tap on the second lookup placeholder (search-where), then tap on the same column again. Again the place holder value changes to the header text. This determines what column LOOKUP searches when trying to Match the value returned by the MAX command. It seems redundant, but it's not. The first command figured out what the maximum value was. This determines what row it is found on.

    Tap the last place holder (result-values) then tap on the bar above the column that contains the values to be compared. In this case C. This determines what column the result comes from. It will be from the same row in the (search-where) column that matched the (search-for) value returned by the MAX function.

    Finally, tap the green checkmark to enter and check the formula. If all went as planed it should work.

    It's a good idea to make a few dummy spreadsheets and practice entering formulas until you get used to the method. It's not all that bad a method, but you need to know what you are going to do before you start, and it does not handle mistakes well.

    Speaking of mistakes, Undo is your friend. It keep you from starting over when something goes wrong.

    Good Luck
    Awesome! I didn't see this post earlier but this worked perfectly.

  6. #25
    iPF Legend
    Member #
    28530
    Liked
    1546 times
    Join Date
    Jan 2011
    Location
    Walla Walla, WA
    Posts
    15,470
    Quote Originally Posted by twerppoet View Post
    Quote Originally Posted by Jmfocus View Post
    After I created the spreadsheet, I clicked on the + tab and created a form and matched it up to the columns on the spreadsheet and formatted the date entry and it autotfilled on the spreadsheet. It was easy and didn't require a formula.
    Ah, a form. I haven't played with those much. I should. Thanks again.
    Hmm, when I created a form and entered new data that way it entirely bolloxed up my formula. It reads the same, but some bug breaks it so that I have to delete the C2 cell, check the formula, then re-add the C2 cell to fix it. Happens every time.

    Give Raymond's method a try instead. Maybe it won't break when using Form entries.

    Sorry.

    This is really annoying! No reason for it to happen.

  7. #26
    iPad Fan!
    Member #
    93425
    Join Date
    Aug 2011
    Posts
    10
    twerppoet, 88raymond

    Thanks to both of you for your help with this problem. I had been working on this for a couple weeks.

  8. #27
    iPad Fan!
    Member #
    93425
    Join Date
    Aug 2011
    Posts
    10
    Quote Originally Posted by twerppoet View Post
    Quote Originally Posted by twerppoet View Post
    Quote Originally Posted by Jmfocus View Post
    After I created the spreadsheet, I clicked on the + tab and created a form and matched it up to the columns on the spreadsheet and formatted the date entry and it autotfilled on the spreadsheet. It was easy and didn't require a formula.
    Ah, a form. I haven't played with those much. I should. Thanks again.
    Hmm, when I created a form and entered new data that way it entirely bolloxed up my formula. It reads the same, but some bug breaks it so that I have to delete the C2 cell, check the formula, then re-add the C2 cell to fix it. Happens every time.

    Give Raymond's method a try instead. Maybe it won't break when using Form entries.

    Sorry.

    This is really annoying! No reason for it to happen.
    Crap you're right!!!

  9. #28
    iPF Legend
    Member #
    28530
    Liked
    1546 times
    Join Date
    Jan 2011
    Location
    Walla Walla, WA
    Posts
    15,470
    Actually every time I enter a new line, form or not, the formula bugs out. It's as if LOOKUP returns zero until I refresh the formula.

    I'm trying variations, but this looks like a major bug in LOOKUP. Sigh

  10. #29
    iPF Legend
    Member #
    28530
    Liked
    1546 times
    Join Date
    Jan 2011
    Location
    Walla Walla, WA
    Posts
    15,470
    Ok, I've managed to put together a different approach MATCH, MAX, and other arcane reference functions. It's at least as complicated as my worst before, but I've tested it and it works with forms, and does not use cell ranges to work.

    If you want it, I'll try to describe the creation. If you've decided to use Raymond's method instead (and if I were not so stubborn I'd have just done that myself), I won't go to the trouble of typing it out.

    Here is the text version, and the screen shot for reference.

    C2-INDIRECT(ADDRESS(MATCH(MAX(DateColumn),DateColumn, find value),3,neither preserved))



    Let me know.
    Attached Thumbnails Attached Thumbnails Help with Numbers formula-valuediffbydate_3.png  

  11. #30
    iPad Fan
    Member #
    80087
    Liked
    5 times
    Join Date
    Jul 2011
    Posts
    285
    too cool! pls post the file. i'd like to see it. thanks!
    iPad2 3G 64GB
    SHSH 4.3.3
    JB
    Manila, Phil


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. Numbers help with formula running down a page
    By BoredFatMan in forum iWork Forum
    Replies: 3
    Last Post: 05-11-2011, 01:19 PM
  3. Mathmatics/Physics Formula writing iPad app
    By fduckworth in forum iPad Apps
    Replies: 6
    Last Post: 05-06-2011, 01:59 AM
  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

dur2days does not work

,

how to subtract in ipad numbers

,

ipad numbers formulas

,

iwork numbers address function help

,

iwork numbers subtraction formula

,
number formulas ipad
,
subtract in numbers apple
,

vlookup

Click on a term to search for related topics.