Excel Formulas! Or And If

[funkylad20]

I have a formula that is basically saying 'If a cell has a number in, use that number, but if it has text in, ignore it' with the additional 'however, if another cell has a number in further along the line, use that instead, if another equivalent cell has text in, ignore it'.

My formula is this:

=IF(A9="","",IF(EN9="No Measure",EN9,
SUM(IF(ISNUMBER(GC9),GC9,GB9),
IF(ISNUMBER(HB9),HB9,HA9),
IF(ISNUMBER(IA9),IA9,HCW9),
IF(ISNUMBER(IZ9),IZ9,IY9),
IF(ISNUMBER(JY9),JY9,JX9))))

All of the IF(ISNUMBER cells are sequential, providing figures over time, and I need to extract the most RECENT number. GC and GB are 12 months and 6 months, HB and HA are 24 months and 18 months, IA and HC are 36 months and 30 months, IZ and IY are 48 months and 42 months and JY and JX are 60 months and 54 months.

So if there is a figure in IY9 but not IZ9, it should provide me with the figure for IY9 (42 Month figure) and disregard figures which will by default be in all cells prior to IY9.

Hope I have explained this ok?! I am thinking I need to remove the SUMIF element, but if I do I just get an error...

[Old_Geezer]

No expert, but I assume you don't have the CR/LFs in.

Try putting part of the formula in and troubleshoot that first, then add terms one at a time checking as you go.

[Old_Geezer]

Maybe the SUM in the middle where it is expecting a test, is causing issues ?

[Old_Geezer]

Ah no I see, forget that one. If I could edit/delete it I would.

[funkylad20]

It's a weird one...I think I need to remove the SUM but if I do, the IF won't work!

[Old_Geezer]

Maybe it's the HCW9. Do columns go that high ?

[Aquariel]

Just a few observations: if the cells are sequential, the middle cell reference should be HZ9 instead of HCW9, which isn't a valid reference. It also seems like you're trying to add up the four cells instead of just choose a value from the most recent one. I think the issue here is that you need an IF and an ELSE type of calculation, which is not exactly what your formula is doing. Work backwards from JY and do it something like this. It checks all 8 cells for a number, with the most recent cell with a number being the result returned:

=IF(ISNUMBER(JY9),JY9,IF(ISNUMBER(JX9),JX9,IF(ISNUMBER(IY9),IY9,etc...)))

You'll need to expand that properly to fit your sheet, but you should get the idea. I hope that's what you mean!

[Old_Geezer]

Curious so had a play.
Yes.

=IF(A9="","",IF(EN9="No Measure",EN9,SUM(IF(ISNUMBER(GC9),GC9,GB9),IF(ISNUMBER(HB9),HB9,HA9),IF(ISNUMBER(IA9),IA9,HCW9),IF(ISNUMBER(IZ9),IZ9,IY9),IF(ISNUMBER(JY9),JY9,JX9)))) seems to be ok.

I may have an earlier version of Excel than you but I think columns over IV don't exist. At least not on mine.

[Old_Geezer]

Oh yes, they do add up, as per the SUM. It was the sum I got from my test. I think a different operator (MAX ?) is needed. Plus some way of extracting the right pair of values once the max is identified. (May need further cells to work out one thing then use it as a pointer.) Getting a bit complex for me, that.