Help with Numbers formula

[twerppoet]

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.

[Ads]

[Jmfocus]

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?

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]

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.

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

[Jmfocus]

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),ValueColumn,2)

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.

[Jmfocus]

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.

[88raymond]

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

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

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.

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

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.

[twerppoet]

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