Excel 2003: Show me the latest value (i think)

[edmundo1971]

I'm using Excel 2003 and I have a simple spreadsheet showing actual cumulative monthly sales (Row 1)against cumulative monthly targets (Row 2).

Row 3 categorises the relevant sales against the appropriate targets as either "above" (if sales are above target), "on" (if sales match target) and "below" (if sales are below target).

What I'm trying to do show the progress from the previous month to the current month and return a value which tells me whether it's the same, whether it's gone up (from either "below" to "on" or from "on" to "above") or whether it's gone down (from either "above" to "on" or from "on" to "below").

The attached spreadsheet should make things clearer. Cell P6 (highlighted yellow)should return a value of "Up" because Nov was below and Dec is above. Cell P6 should then beable to return the appropriate value once Jan is completed.

I appreciate that there mightn't be a single solution and that I may have to devise a formula which returns just the latest and the previous months position and then another to compare the two. I've already tried doing this using =IF(COUNTA(C6:N6)=0,"",IF(COUNTA(C6:N6)=1,C6... (and so on) but it seems to not want to play once I get past six conditions.

Any help would be much appreciated.

And I apologise if title I've given is a bit unclear.

Thanks

Ed

example1.xls

[etaf]

try

="From "&INDIRECT(ADDRESS(6,MATCH("*",$A6:$N6,-1)-1))&" to "& INDIRECT(ADDRESS(6,MATCH("*",$A6:$N6,-1)))

[RobertMika]

=lookup(2,1/(c6:n6<>""),c6:n6)

[edmundo1971]

Wow - many, many thanks!!!

My limited Excel brain has no idea how to translate that formula into something I undersatnd and something I could use but still, wow!!!

How would I go about amending the formula so that just either "up", "down" or "no change" was displayed???

Thanks again!!!

Ed

[edmundo1971]

RobertMika

To you also goes a "wow - many, many thanks!!!".

Similarly to my reply to etaf's reply, I'm not massively familiar with the LOOKUP function so trying to understand how it works might take me some time (but I'll give it a go).

Again, could your formula be amended so that just either "up", "down" or "no change" was displayed???

Thanks

Ed

[etaf]

i await RobertMika, as that result is simplier to get the last entry than i have , and my excel brain has not yetworked out
I would use a lookup table to do that , as your not able to nest enough IFs
or maybe a lookup array may work - but i would create a huge formula

[RobertMika]

=LOOKUP(LOOKUP(2,1/(C6:N6<>""),C6:N6),{"above","below","on"},{"up","down","no change"})

[edmundo1971]

etaf

You solution returns the correct result in that it shows the progress from the previous month (i.e. from "below" in Nov to "above" in Dec). If I can understand the code and can somehow get it to return just "up", "down" or "no change" (depending upon how the previous month relates to the current) then I'll have my answer.

RobertMika

You solution gives me the other outcome i was possibly looking for in that it returns just the latest month. Again, if I can get my head around the code I'd like to tjhink that I could write something similiar (it's hugely important to me that i understand the formulas I'm using so that I can adpat them as I go on) to return the prevous month's outcome then I can add another simply formual to return "up", "down" or "no change" depending upon the relationship between the previous and current months.

Any help translating the LOOKUP formaula would be much appreciated because I can't for the life of me understand what Microsoft have on their Excel Help pages :-)

[etaf]

i have been researching RobertMika formula - as i to wanted to understand
http://www.excel-user.com/2012/07/lo...-criteria.html
and this pretty much explained it to me - but still looking

so how does mine work
i have been using a site with a number of tutorials and one is how to find the last row/column with data in
http://www.grbps.com/Excel7.pdf

and so to find the column or row ID
that has the last data in your case
row
then I used
MATCH("*",$A6:$N6,-1)
by starting at A - it gives the correct number of column
so returns a number for the column

to turn that into an actual cell reference, i used
ADDRESS(6,MATCH("*",$A6:$N6,-1)))
Address returns the cell address

Address(row,column)
so 6 is row 6
and the Match returns the column

so we get
$I$6
for example

now i want to use the value that is in that cell and indirect will return the cell contents and allow those contents to be used in a formula
indirect()

then you get in full
INDIRECT(ADDRESS(6,MATCH("*",$A6:$N6,-1)))
which returns the cell
before the last blank cell
to get the one before - i need to subtract one from the column number
MATCH("*",$A6:$N6,-1)-1

then i use concatenate &
to join them all together

[etaf]

as you cant use an extended nested IF - above seven
and you have a few combinations to consider
below - below
below - on
below - above
on - below
on - on
on - above
above - below
above - on
above - above

and so the result you want is

below - below = no change
below - on = up
below - above = up
on - below = down
on - on = no change
on - above = up
above - below = down
above - on = down
above - above = no change

then I would use my two indirects to create a lookup and then a table to return the result

Table is in B25:C33

=VLOOKUP(INDIRECT(ADDRESS(6,MATCH("*",$A6:$N6,-1)-1))&" - "& INDIRECT(ADDRESS(6,MATCH("*",$A6:$N6,-1))), $B$25:$C$33, 2, FALSE)

said it was complicated

[edmundo1971]

etaf

Many thanks again!!!

Sorry I couldn't reply yesterday but I'd let work by then. I'll have a good look over your last two posts later today once I've got my DIY jobs outta the way :-)

Speak shortly and thanks again.

Ed

[edmundo1971]

etaf,

Apologies for not replying earlier.

I tried to unravel your formula with the help of a colleague form another office (hence the delay). The upshot was that I still couldn't get my head round it but you and RobertMika did get me thinking and I eventually came up with what is probably a longer solution but one which I can get my head around (and which will hopefully enable me to understand both your solutions).

Having chosen to use a LOOKUP formula, I began by numbering the months from 1 to 12. Then I used a simple COUNT formula to return a value equal to the number of non-blank cells on the 'Sales' row. Next, I used a LOOKUP formula which, using the result of the COUNT formula to identify the appropriate month, returned appropriate outcome (either 'above', 'on' or 'below').

A copy of the COUNT formula, amended to return a value equal to the number of non-blank cells on the 'Sales' row minus 1, allowed a second LOOKUP formula to return the appropriate outcome of the previous month, and then a simple (but lengthy) IF formula to compare the two outcomes enabled me show the progress (either 'up', 'no change' or 'down').

The result can be seen in the attached, should you be interested (the purple cells refer).

Again, thanks for yoy help and assistance in this matter.

Ed

[etaf]

thanks for the rep and the feedback, sorry you and your collegue did not follow my solution, but glad you have it solved now