ISNUMBER and IF Functions

S

spunkyjon

Ok, lets see if i can explain this one well enough...

I have 3 columns, A,B and C. In these columns are numbers. In Column D
I want to apply the following formula: =A1+MAX(B1/365,C1/6)

This works however the problems arrise as there are some cells in
columns A:C which are blank or have text not numbers. I tried to fix
this with this formula: =ISNUMBER(A1:C1),A1+MAX(B1/365,C1/6)

This works however if there is no number in any one of the three cells
it returns a "FALSE" error. I would prefere it to display nothing, ie
"". But i can't seem to do this.

Other more complex problems: if there is one number in either column B
or C i would like it to complete the formula using this number. There
must always be a number in column A. If there is no number in A then
the result should be blank.

example:

A B C D
4 6 7 calculate
6 7 Nothing
text 6 7 Nothing
4 text 7 calculate
4 Nothing
4 text text Nothing
4 text Nothing


Hopefully someone can help me. It is driving me crazy!

Jon Scott
 
L

Leo Heuser

spunkyjon said:
Ok, lets see if i can explain this one well enough...

I have 3 columns, A,B and C. In these columns are numbers. In Column D
I want to apply the following formula: =A1+MAX(B1/365,C1/6)

This works however the problems arrise as there are some cells in
columns A:C which are blank or have text not numbers. I tried to fix
this with this formula: =ISNUMBER(A1:C1),A1+MAX(B1/365,C1/6)

This works however if there is no number in any one of the three cells
it returns a "FALSE" error. I would prefere it to display nothing, ie
"". But i can't seem to do this.

Other more complex problems: if there is one number in either column B
or C i would like it to complete the formula using this number. There
must always be a number in column A. If there is no number in A then
the result should be blank.

example:

A B C D
4 6 7 calculate
6 7 Nothing
text 6 7 Nothing
4 text 7 calculate
4 Nothing
4 text text Nothing
4 text Nothing


Hopefully someone can help me. It is driving me crazy!

Jon Scott
Click to expand...

Hi Jon

Try this one:

=IF(NOT(ISNUMBER(A1)),"",IF(AND(ISNUMBER(B1),NOT(ISNUMBER(C1))),
A1+B1/365,IF(AND(NOT(ISNUMBER(B1)),ISNUMBER(C1)),A1+C1/6,
IF(AND(ISNUMBER(B1),ISNUMBER(C1)),A1+MAX(B1/365,C1/6),""))))

assuming 6 7 Nothing should have been 6 7 Calculate (number in B)
 
P

paul

=IF(A1="","",IF(AND(NOT(ISNUMBER(B1)),ISNUMBER(C1)),A1+C1/6,IF(AND(NOT(ISNUMBER(C1)),ISNUMBER(B1)),A1+B1/365,A1+MAX(B1/365,C1/6))))
i think this covers what you have posted.There may be another couple of
conditions tho.Could there be non numbers in B and C?.Will b1/365 and c1/6
ever be equal?
 
S

spunkyjon

Thanks guys that worked like a charm!!!

:)

=IF(A1="","",IF(AND(NOT(ISNUMBER(B1)),ISNUMBER(C1)),A1+C1/6,IF(AND(NOT(ISNUMBER(C1)),ISNUMBER(B1)),A1+B1/365,A1+MAX(B1/365,C1/6))))
i think this covers what you have posted.There may be another couple of
conditions tho.Could there be non numbers in B and C?.Will b1/365 and c1/6
ever be equal?
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Help with an Excel macro please? 6
Display weeks from a Milestone 0
excel copy and paste... 2
Arriving at totals 2
Page numbering with Merged Document and IF statements 1
Search cell value in same column 3
Column multiplied by a matrix 2
Looking to added up based on a character from cell range 1

Top