Help with Numbers formula

[Jmfocus]

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

[Sponsors]

[twerppoet]

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.

[Jmfocus]

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.

[twerppoet]

That sounds about right. I'll see what I can figure out.

[twerppoet]

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.

[88raymond]

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

[twerppoet]

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

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.

[88raymond]

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).

[twerppoet]

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.

[twerppoet]

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.