Thread
:
ISNUMBER and IF Functions
View Single Post
#
4
Posted to microsoft.public.excel.misc
spunkyjon
external usenet poster
Posts: 7
ISNUMBER and IF Functions
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
Reply With Quote
spunkyjon
View Public Profile
Find all posts by spunkyjon