Help with Numbers formula

[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

 

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

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.

 

[twerppoet]

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.

 

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

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.

 

[Jmfocus]

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.

 

[twerppoet]

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.

 

[Jmfocus]

twerppoet, 88raymond

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

 

[Jmfocus]

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

 

[twerppoet]

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

 

[twerppoet]

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.

 

[88raymond]

too cool! pls post the file. i'd like to see it. thanks!