i know excel cannot loop so wondering if there is another way to do this: I have a list of cummulative percentages say in A1:A12, and i want the last non zero percentage to be displayed in another excel sheet. the way i had thought of doing it was:

=IF(G16="",G15,IF(G15="",G14,IF(G14="",G13,IF(G13="",G12,G16))))

and so on but this doesnt work.

the data to generate each percentage is added each hour so need the cumulative percentage that is updated at each point.

✔ Best Answer

I am assuming that the last value in the list will always be the minimum value in the list. If that is the case, try this:

=INDIRECT(CELL("address",INDEX(A1:A12,MATCH(MIN(A1:A12),A1:A12,0))))

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Please post an example of the data that is A1:A12 and what value you want returned based on that example.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

in A1:A12 i have calculated percentages. for examples sake 100%:89% and as 89% is the last in the list i want this number to be shown in another cell. However each row is updated every hour so at the 1st hour A1= 100% and A2:A12 will be blank and as A1 will be the last in the list this is the number i want to be shown in this other cell.

So if A1:A11 are 100%:88% and A12 is blank i want some formula to to find the last number in the list(A1:A12) and display it.

The answer may depend how the calculated percentages end up in A1:A12. Please explain that.

Please post the formula if one is being used.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

the formula is =IF(C6="", "", (F6/E6))

I am assuming that the last value in the list will always be the minimum value in the list. If that is the case, try this:

=INDIRECT(CELL("address",INDEX(A1:A12,MATCH(MIN(A1:A12),A1:A12,0))))

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Ask Your Question

Weekly Poll

Have you played Jackbox during the pandemic?

Discuss in The Lounge

Poll History