Look up a vaule that is not zero

karenO

New Member
Joined
Apr 15, 2002
Messages
47
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

:p
 

Excel Facts

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

karenO

New Member
Joined
Apr 15, 2002
Messages
47
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
Joined
Jun 19, 2003
Messages
7,985
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
Joined
May 19, 2002
Messages
8,458

ADVERTISEMENT

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
Joined
Feb 24, 2003
Messages
1,790
Office Version
  1. 2016
  2. 2013
  3. 2011
  4. 2010
  5. 2007
Platform
  1. Windows
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
Joined
Aug 26, 2006
Messages
82
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
Joined
Jun 19, 2003
Messages
7,985
Nice one jimrward, I was wondering how to get around the IF statement limit of 7 IF's.
 

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top