divide by 2 returning #VALUE! if blank cell

[MuddyStone]

Hi,

I'm trying to write a formula in cell V33 of my spreadsheet that looks at two other cells...

Cell R33 = profit made on a commodity
Cell U33 = number of days the commodity was owned

First of all, if R33 is blank, then I want V33 to be blank as well

If the commodity was owned for less than 365 days (U33), then I want to return the same profit value from R33 in V33
However, if the commodity was owned for more than 365 days (U33), then I want to divide the profit value in R33, by 2

This is the formula I'm using...

=IF(ISBLANK(R33),"",IF(U33>365,R33/2,R33))
It works perfectly unless R33 is blank, i.e. = 0 and the commodity is owned for more than 365 days, in which case the cell returns #VALUE!

I've tried IFERROR but can't seem to get my syntax right? Any help appreciated.

Thanks
Muddy

 

[Michael M]

Make sure R33 is a Number and NOT a text version of that number
Otherwise works fine for me

 

[MuddyStone]

Make sure R33 is a Number and NOT a text version of that number
Otherwise works fine for me

Ahh... thank you Michael, R33 is actually a blank cell because it has it's own ISBLANK statement. I changed its isblank formula to return a zero instead of a blank and suddenly, my V33 formula worked.

Would prefer to have been able to keep R33 blank instead of returning a zero but I can with it.

Thanks again

 

[Michael M]

Try using

=IF(IR33="","",IF(U33>365,R33/2,R33))

 

[MuddyStone]

Try using

=IF(IR33="","",IF(U33>365,R33/2,R33))

You nailed it! Thanks so much!

Mark

 

[johnnyL]

If(IR33 WTF?

 

[Michael M]

glad to help...
and shoulda been,without the "I"

=IF(R33="","",IF(U33>365,R33/2,R33))

 

[Michael M]

@johnnyL
Yeh, yeh......just too picky about my typing !!...?