Look up a vaule that is not zero

karenO

New Member
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

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

SteveO59L

Well-known Member
How many months data will be held ?

karenO

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

Karen

Datsmart

Well-known Member
Try this formula in the Current Column and copy down.
Code:
``=IF(E2<>"",E2,IF(D2<>"",D2,IF(C2<>"",C2,B2)))``
In my test page the formula goes in column F.

texasalynn

Well-known Member

try this:
Book2
ABCDEF
1Month 1Month 2Month 3Month 4Current Score
2Site175%75%75%
3Site20%80%80%80%
4Site390%65%65%65%
Sheet1

formula in F2
Code:
``=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

Well-known Member
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

Board Regular
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:
Code:
``=INDEX(B1:M1,1,COUNTA(B1:M1))``
All this does is simply display the last value entered in the row.

Datsmart

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

Replies
4
Views
319
Replies
13
Views
324
Replies
2
Views
268
Replies
0
Views
128
Replies
0
Views
91

1,136,651
Messages
5,677,004
Members
419,667
Latest member
MegEri

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back