Look up a vaule that is not zero

[karenO]

I have audit scores which may or may not be updated each month I then need a column that looks along and gives me the most up to date scores

eg

Month 1 Month 2 Month 3 Month 4 Current Score
Site1 75% 75%
Site 2 0% 80% 80%
Site 3 90% 65% 65%


Is there a formula I can put in the Current Score Column

Probably simple but I am a little dim

Thanks

 

[SteveO59L]

How many months data will be held ?

 

[karenO]

12 months with an extra column for the previous year score too so its sort of like a running total

Karen

 

[Datsmart]

Try this formula in the Current Column and copy down.

=IF(E2<>"",E2,IF(D2<>"",D2,IF(C2<>"",C2,B2)))

In my test page the formula goes in column F.

 

[texasalynn]

try this:

Book2
	A	B	C	D	E	F
1		Month 1	Month 2	Month 3	Month 4	Current Score
2	Site1	75%	75%			75%
3	Site2	0%	80%	80%		80%
4	Site3	90%	65%	65%		65%
Sheet1

formula in F2

=IF(AND(NOT(ISBLANK(E2)),E2<>0),E2,IF(AND(NOT(ISBLANK(D2)),D2<>0),D2,IF(AND(NOT(ISBLANK(C2)),C2<>0),C2,IF(AND(NOT(ISBLANK(B2)),B2<>0),B2,""))))

there are other methods for this, so you might try doing a search on this forum for suggestions

 

[jimrward]

you could use the offset function and use the count of the number of occupied cells per row as follows

=OFFSET(A2,0,COUNT(B2:D2)) assuming site1 is in A2 and current score column is E

put the above in E2 and copy down, adjust the B2:D2 accordingly

Month 1 Month 2 Month 3 Month 4 Current Score
Site1 75% 75% 75.00%
Site2 0% 80% 80% 80.00%
Site3 90% 65% 65% 65.00%

 

[xapie128]

This answer is based on the following assumptions:

• Column A has Site X (not part of the answer that you want in the Current Score column)
  
  Columns B through M (12 in all) are the months
  
  Column N contains the Current Score, which is the latest value entered in each row
  
  All cells to the right of your last data entered in any row are blank, that is they don't contain a space, a non-printable character, or a formula

Place the following in cell BN (change the "1"s if not in row 1, adjust the "B"s to match your starting column, adjust the "M"s to match your ending column) then copy down:

=INDEX(B1:M1,1,COUNTA(B1:M1))

All this does is simply display the last value entered in the row.

 

[Datsmart]

Nice one jimrward, I was wondering how to get around the IF statement limit of 7 IF's.