Help with Numbers formula

Discussion in 'iWork Forum' started by Jmfocus, Aug 29, 2011.

  1. Jmfocus
    Offline

    Jmfocus iPF Novice

    Joined:
    Aug 29, 2011
    Messages:
    10
    Thanks Received:
    0
    Trophy Points:
    0
    Ratings:
    +0 / 0
    Hello everone, I am new to the forum, recently purchased my first Ipad and here I am.

    What I am trying to do is write a formula that will take the value of C2 and minus the most recent log entry at the bottom of column C and then updat in the footer. So that everytime I update the log, it updates the value in the footer. I have been researching this for days and can't get anything to work.

    Thanks for your help
  2. twerppoet
    Online

    twerppoet iPad Legend

    Joined:
    Jan 8, 2011
    Messages:
    16,403
    Thanks Received:
    2,032
    Trophy Points:
    113
    Location:
    Walla Walla, WA
    Ratings:
    +2,316 / 1
    An interesting challenge. I'll give it a go. It would help if you could supply the basic file you want this to happen in (minus any private data) with a more detailed description of what exactly goes in what cell.

    You'll need to make two more posts before you can post a link, so brows around in the forum and enjoy. You are bound to find something you want to comment on.
  3. Jmfocus
    Offline

    Jmfocus iPF Novice

    Joined:
    Aug 29, 2011
    Messages:
    10
    Thanks Received:
    0
    Trophy Points:
    0
    Ratings:
    +0 / 0
    Thanks Twerpoet. Basically I have a beginning number in C2 that corresponds with a date. Say that number is 100. Now in C3 there is a number 99. C4 has 98.65 etc. I have a footer that I would like to have update the differance between the first number "100" and the following numbers depending on the date of entry. So if 100 was input on 8/25 and 99 was input on 8/26 then the footer would read "1". On 8/27 the input number would be 98.65 and the footer would read 1.35 etc.

    Hope that helps.

    I think the formula would be C2- LOOKUP.... then something else that would grab the last entry in column C.
  4. twerppoet
    Online

    twerppoet iPad Legend

    Joined:
    Jan 8, 2011
    Messages:
    16,403
    Thanks Received:
    2,032
    Trophy Points:
    113
    Location:
    Walla Walla, WA
    Ratings:
    +2,316 / 1
    That sounds about right. I'll see what I can figure out.
  5. twerppoet
    Online

    twerppoet iPad Legend

    Joined:
    Jan 8, 2011
    Messages:
    16,403
    Thanks Received:
    2,032
    Trophy Points:
    113
    Location:
    Walla Walla, WA
    Ratings:
    +2,316 / 1
    Ok, here is what I came up with. It's not perfect, because it won't deal with gaps or cells with invisible entries (like spaces). You should be able to reduce the second by formatting the entire column as dates, and will just have to be careful about the first.


    These instructions are inside out (first described, last done when creating the formula). Follow along best you can using the attached screen shot.

    First you use COUNTA, to determine the number of entries in the column, add 1 to offset it to the actual row number (since it does not count the Header row. You select the row by taping the bar above the row, not one of the cells.

    Next you use ADDRESS to turn the result into a location string. The COUNTA result becomes the row, and I manually enter a 2 for the column (because I'm using column B). Change the addr-type to 'neither preserved' to get a typical 'B5' result. Otherwise you end up with $B$5, which wont work with the next command. Carefully edit (delete) the other values and commas.

    INDIRECT fetches the value of the cell address created by the ADDRESS function.

    Subtract the beginning cell value from your your current value (or vise versa depending on whether you want negative or positive results. I opted for the former, because it was slightly easier to edit. (If you want the later, just insert the cursor before instead of after the formula, select the cell, and then tap minus). Hope that made sense. At any rate, the B2 would appear directly after DUR2DAYS instead of at the end of the equations, just before the last parentheses.

    Finally the result needs to be a duration. I figured days (for a log) would be the most logical. DUR2DAYS converts the results of B5-B2 into the number of days.

    Hope this helps.

    Attached Files:

  6. 88raymond
    Offline

    88raymond iPad Fan

    Joined:
    Jul 22, 2011
    Messages:
    285
    Thanks Received:
    5
    Trophy Points:
    0
    Ratings:
    +5 / 0
    are the succeeding numbers always smaller than its predecessor? you can always use the minimum function :)

    c2-min(c3:c99)

    if not, if the dates are in column b,

    =c2-vlookup(max(b2:b99),b2:c99,2)



    note: this assumes your footer is on c100
    Last edited: Aug 29, 2011
  7. twerppoet
    Online

    twerppoet iPad Legend

    Joined:
    Jan 8, 2011
    Messages:
    16,403
    Thanks Received:
    2,032
    Trophy Points:
    113
    Location:
    Walla Walla, WA
    Ratings:
    +2,316 / 1
    Thanks!

    That's much better than my formula, as long as new entries are always either greater or lesser than the original date. I enclose it with the DUR2DAY function to get rid of the trailing d, and so the result will be a number that can be used in other equations.

    I found it unnecessary to use VLOOKUP, since MAX returns the value directly. Or am I missing something?

    My final formula, based on increasing dates from the original.

    DUR2DAY(MAX(Dates)-C2) Where Dates is the name of the column. You can avoid using cell ranges if you select the entire column instead. Do this by tapping on the bar above the column instead of a cell. The function entry will become the Header of the column. As long as the formula is located in the footer, it will not be counted in the column range, and always at the bottom of the table.

    EDIT: Added screen shot, just because I did it before. Note that in the screen shot the column's name is End, not Date. I thought I was being clever by changing the name to something that made more sense than my cobbled together test table.

    Attached Files:

    Last edited: Aug 29, 2011
  8. 88raymond
    Offline

    88raymond iPad Fan

    Joined:
    Jul 22, 2011
    Messages:
    285
    Thanks Received:
    5
    Trophy Points:
    0
    Ratings:
    +5 / 0
    i don't have the ability to comprehend your solution, it seems too complex, hehe..

    as i understand it, the OP has two sets of data: dates and values. each date has a corresponding value. he wants to always have the difference between the latest value entry with the original entry.

    i assumed the latest entry would be the highest date value, thus the "max" function in my second formula. vlookup is needed because of the array (two sets of values).
    Last edited: Aug 30, 2011
  9. twerppoet
    Online

    twerppoet iPad Legend

    Joined:
    Jan 8, 2011
    Messages:
    16,403
    Thanks Received:
    2,032
    Trophy Points:
    113
    Location:
    Walla Walla, WA
    Ratings:
    +2,316 / 1
    Headsmack! You are right, the values, not the dates themselves are supposed to be compaired. I don't know where I got off track. I still think your formula will work better if you select the columns instead of a cell range, but have not tested it to be sure.

    My first formula was way to complex, which is why I liked your use of MAX or MIN so much. I had not stumbled on those yet.
  10. twerppoet
    Online

    twerppoet iPad Legend

    Joined:
    Jan 8, 2011
    Messages:
    16,403
    Thanks Received:
    2,032
    Trophy Points:
    113
    Location:
    Walla Walla, WA
    Ratings:
    +2,316 / 1
    Ok, I had to go back and experiment some more. Maybe I've got some OCD hiding in my genes.

    I could not get the column selection thing to work with your formula. However, when I used LOOKUP instead of VLOOKUP it worked just fine. Here is a screen shot of the results.

    I do see one potential problem not mentioned yet. I don't know what the formula would do if you entered two identical dates. The results could be unpredictable. It could be avoided by entering the time as well, I suppose, but it would be easy to overlook.


    Edit: Oops, notice I have D14 selected for the formula, but it is exactly the same formula instead as C14. I'd been playing with VLOOKUP in the D14 column because I was getting self-referencing errors in C14. I got it to work using LOOKUP, then copy pasted the formula to C14 where it worked just as well.

    Attached Files:

    Last edited: Aug 30, 2011
  11. twerppoet
    Online

    twerppoet iPad Legend

    Joined:
    Jan 8, 2011
    Messages:
    16,403
    Thanks Received:
    2,032
    Trophy Points:
    113
    Location:
    Walla Walla, WA
    Ratings:
    +2,316 / 1
    And finally, just because I am a total glutton for punishment, here is what it looks like if you go back to my method for finding the last entry in the table (as opposed to newest date).

    Basically you replace the simple MAX statement with the INDIRECT statement (and everything inside it). Painful, unless you really need the last entry, and not just the most recent date.

    Attached Files:

  12. Jmfocus
    Offline

    Jmfocus iPF Novice

    Joined:
    Aug 29, 2011
    Messages:
    10
    Thanks Received:
    0
    Trophy Points:
    0
    Ratings:
    +0 / 0
    Guys thanks for helping me out. So far nothing is working. The solution below has been added to the footer line and shows only the actuall formula in the cell instead of making the computation.

    The way my worksheet is set up is that everytime I add data it automatically adds a line to corrospond with the date. So I could have 500 entries when 500 days pass but the footer always remains at the bottom. I created the footer by touching the first cell in the spreadsheet which gives you the ability to maniputate the number of rows and columns, then touching the circle with a little "i" which brings up a window that will allow for a footer to be added and always kept at the bottom of the spreadsheet.

    Twerppoet your solution was way over my head but thanks for your help. Do you guys think this is possible with the footer?



  13. twerppoet
    Online

    twerppoet iPad Legend

    Joined:
    Jan 8, 2011
    Messages:
    16,403
    Thanks Received:
    2,032
    Trophy Points:
    113
    Location:
    Walla Walla, WA
    Ratings:
    +2,316 / 1
    Look at the second to last solution, not my very last. That was just an exercise in craziness. The only real difference between it and Raymond's solution is that I use the LOOKUP function instead of VLOOKUP, and I use column selections instead of cell ranges.

    VLOOKUP kept including the header and footer cells in the search, so it failed when using columns. LOOKUP only sees the non-header/footer cells, so it could be set by tapping the entire column instead of needing to select a range of cells. This is more convenient since you don't have to worry about increasing the cell range latter if you make more entries than you originally planned.

    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.
    Last edited: Aug 30, 2011
  14. twerppoet
    Online

    twerppoet iPad Legend

    Joined:
    Jan 8, 2011
    Messages:
    16,403
    Thanks Received:
    2,032
    Trophy Points:
    113
    Location:
    Walla Walla, WA
    Ratings:
    +2,316 / 1
    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.
  15. Jmfocus
    Offline

    Jmfocus iPF Novice

    Joined:
    Aug 29, 2011
    Messages:
    10
    Thanks Received:
    0
    Trophy Points:
    0
    Ratings:
    +0 / 0
    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.
  16. Jmfocus
    Offline

    Jmfocus iPF Novice

    Joined:
    Aug 29, 2011
    Messages:
    10
    Thanks Received:
    0
    Trophy Points:
    0
    Ratings:
    +0 / 0
    I created a separate tab where I enter the daily info and it matches with the spreadsheet.
  17. 88raymond
    Offline

    88raymond iPad Fan

    Joined:
    Jul 22, 2011
    Messages:
    285
    Thanks Received:
    5
    Trophy Points:
    0
    Ratings:
    +5 / 0
    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... http://www.4shared.com/file/58uCnUby/Blank.html. 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...

    Attached Files:

    Last edited: Aug 30, 2011
  18. twerppoet
    Online

    twerppoet iPad Legend

    Joined:
    Jan 8, 2011
    Messages:
    16,403
    Thanks Received:
    2,032
    Trophy Points:
    113
    Location:
    Walla Walla, WA
    Ratings:
    +2,316 / 1
    Thanks. I'll play with it.

    I like the idea of keeping result data in the header. I may have to use that myself sometime.
  19. twerppoet
    Online

    twerppoet iPad Legend

    Joined:
    Jan 8, 2011
    Messages:
    16,403
    Thanks Received:
    2,032
    Trophy Points:
    113
    Location:
    Walla Walla, WA
    Ratings:
    +2,316 / 1
    Ok, I might be able to figure that out. Thanks.
  20. twerppoet
    Online

    twerppoet iPad Legend

    Joined:
    Jan 8, 2011
    Messages:
    16,403
    Thanks Received:
    2,032
    Trophy Points:
    113
    Location:
    Walla Walla, WA
    Ratings:
    +2,316 / 1
    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
    Last edited: Aug 30, 2011

Share This Page

Search tags for this page
adding and subtracting dates in numbers iwork
,

dur2days does not work

,
how do i add or subtract on numbers ipad app
,
how to but a formula into full column in ipad numbers
,

how to subtract cells in iwork numbers

,

how to subtract in ipad numbers

,
how to write a function in numbers on ipad
,
how to write formula to add percent in number on ipad
,
ipad air number how to make formulas
,

ipad numbers formulas

,
iwork address function
,

iwork numbers subtraction formula

,

mac numbers subtraction function

,
minus function on numbers ipad
,
numbers address function
,
numbers ipad calculation
,
subtract imac numbers
,
subtract in numbers apple
,
using formulas in i pad numbers
,
what formula to subtract in imac numbers?