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

"spunkyjon" skrev i en meddelelse
ps.com...
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


Hi Jon

Try this one:

=IF(NOT(ISNUMBER(A1)),"",IF(AND(ISNUMBER(B1),NOT(I SNUMBER(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)


--
Best regards
Leo Heuser

Followup to newsgroup only please.

=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?
--
paul

remove nospam for email addy!



"spunkyjon" wrote:

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

Thanks guys that worked like a charm!!!

:)


paul wrote:
=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?
--
paul

remove nospam for email addy!



"spunkyjon" wrote:

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

"spunkyjon" skrev i en meddelelse
oups.com...
Thanks guys that worked like a charm!!!

:)

You're welcome