Substituting cells formula???

Greetings All,

I have a formula question that I can't seem to find the answer for.

I want a "total" cell to check two cells. If cell 1 has a number, display it.

If cell 1 does not have a number, check cell 2 and display it.

If either cell does not have a number, display a zero.

Thanks in advance for your help...

Cheers - OS

Thanks for the reply...

It works with the first cell, but does not work with the second cell.

=IF(ISNUMBER(L8),L8,IF(ISNUMBER(N8),N8,0))

I've learned a great deal reading through this forum. Glad I found it...

Thanks again,

OS

What do you mean "....does not work with the second cell"? If N8 has a number and L8 doesn't you want to show the number in N8, are you saying that doesn't work with your data?

It works for me. I put ratcat's formula in O8, left L8 blank and then put 10 in N8, O8 shows 10, isn't that what you require?

Both cells L8 and N8 are simplistic formulas.

Would that be causing the issue?

Thanks,

OS

What formulas do you have in those cells? Functions like MID, LEFT, RIGHT and others always return a text result (even if it looks like a number) so the ISNUMBER part of Ratcat's formula might not recognise a number as you might expect. You could probably alter L8 and N8 to return genuine numbers.......or try changing to

=IF(ISNUMBER(L8+0),L8+0,IF(ISNUMBER(N8+0),N8+0,0))

Tried that as well, and still won't show N8

Here is what makes those cells populate:

L8 is calculated with =SUM(H8)*K8

N8 is calculated with =SUM(J8)*M8

Both L8 and N8 are currency fields, if that makes a difference.

I think I'm just missing the logic here, or I haven't had enough coffee.

Thanks again...

OS

Can you give a concrete example where the formula doesn't work as you want - what's in L8, what's in N8 and what do you want the formula to return.....and what does it return?

L8 and N8 are totals for a construction process. Let's say L8 is option 1 and N8 is option 2.

If L8 has a number, that number needs to be shown in P8 (total for that option).

If L8 does not have a number, I need the formula to check N8. If that has a number, it needs to be shown in P8.

Right now, with the formula given, if L8 has a number, it is shown in P8

If L8 does not have a number, but there is a number in N8, P8 still show 0

Does this make sense?

OS

That did it...

Thank you so much!!!

=IF(L8<>0,L8,IF(N8<>0,N8,0))

I'm still not understanding the logic, but at least I can continue the project.

Thanks for making my Sunday!!!

OS