How to enter the value of the cell immediately above if that cell = 0

[blueshevy]

Hi
I am in the last stages of setting up a spreadsheet which will help calculate scores in a game we play and need help with the following:

If, within a column of random numbers (calculated elsewhere) there is a 0, what is the value of the number in the cell immediately above the 0, and if more than one 0 in the column, the greatest of these.

I hope that makes sense.

Cheers and thanks in advance
Jan

[blueshevy]

Oh, I should add - I am using Excel 2000 (okay, I'm cheap and have an OLD computer )

[zbor]

Hi blueshevy, can you please rename your title according to the rules:

1. Your post title should accurately and concisely describe your problem, not your anticipated solution. Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.

To change the title of the thread, click EDIT on the original post, then click the Go Advanced button, then change the title. If two or more hours have passed, the EDIT button will not appear, and you need to ask a moderator to change the title.

[blueshevy]

Hi blueshevy, can you please rename your title according to the rules:

1. Your post title should accurately and concisely describe your problem, not your anticipated solution. Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.

To change the title of the thread, click EDIT on the original post, then click the Go Advanced button, then change the title. If two or more hours have passed, the EDIT button will not appear, and you need to ask a moderator to change the title.

Thanks zbor - I hope that is better

[zbor]

Here try this:

Assume your data is in first row:

=MAX(IF($A$1:$AZ$1=0,$B$1:$BA$1))

comfirmed with ctrl+shift+enter

(adjsut range to your data just notice that if data starting in A column and ending in X column you need to take one more in other range B to Y)

[DonkeyOte]

If as implied the values are in a Column then you would adapt zbor's range accordingly such that you check for 0 from the 2nd row to the last and where 0 use the cell above before conducting the MAX, ie:

Please Login or Register  to view this content.

(you must start from 2nd row rather than first given if first row is 0 there is no row 0 to review...)

[blueshevy]

okay, now I'm REALLY confused.

Attached is a sample of what I am trying to do. What I would like my/your calculation to come up with is 486, i.e., the greatest number immediately preceeding a 0.

Sorry to seem a bit dim, my brain is hurting - I have been trying to work this out for ages now. I am really a beginner at all this.

Thanks heaps
Jan

[DonkeyOte]

You don't specify where you want the result, nor the significance (if any) of the values in C / blank line interspersed between groups of data, however in general terms

Please Login or Register  to view this content.

that in red is crucial - ie commit using the above keystrokes and not Enter as you would normally... this is an Array formula.

the above is just an adapted version of my prior post (in itself an adaptation of zbor's) - ie altering the range references.

[blueshevy]

OMG I think I love you!!!!

Thanks so much guys, that is exactly what I was after. I think I will name my first born after you - no wait, too late! I will at the very least give you credit when all my people tell me how clever I am to come up with this.

Cheers
Jan

[zbor]

What if there's no number (empty cell)... This formula treat is as 0...

[DonkeyOte]

Either

a) add an ISNUMBER test

Please Login or Register  to view this content.

b) use coercion to string

Please Login or Register  to view this content.

[blueshevy]

Actually guys I just changed the sheet so there was no empty rows. Much simpler.

Thanks again