If value is true, use a formula else display nothing.

clintonmadden

Registered User.
Local time
Yesterday, 19:25
Joined
Mar 12, 2010
Messages
26
Hi,

I'm trying to find a formula that will only calculate if the data in an adjacent cell is greater that 0 and if not, the cell will remain blank. I'm not sure if I'm going about it in the right way, but I came up with:

=IF(C8<0,(C8*1.175),""), but the cell remains blank no matter what I put in cell C8.

Anyone have any ideas?

Thanks
View attachment Productive.xls
 
Last edited:
Well, to start with the formula is saying 'if C8 is LESS than 0' not greater than - I've just copied it into Excel and if you put a negative value in C8 it works OK.

Try changing it to
Code:
=IF(C8>0,(C8*1.175),"")

and see what happens.
 
Also, the "" is putting a zero-length string into the cell, when you'd probably want a number. You could try a 0 or just leaving the if-false condition out altogether.
 
Try and change C8<0 to C8>0 as you have it now it checks C8 for numbers LOWER than 0 aka negative numbers

JR
 
Is there an echo in here? Or is it just me? ME.... Me.... me....
 
Hi Guys, thanks for the replies, it worked perfectly. However there's another problem. I have another formula which I want to calculate and also display a blank if there is no value in the cell. But the cell in question is the one I have just made the equation for, which you said has inserted a string of 0 length. Does that mean the cell isn't less than zero? =IF(C8>0,(C8*1.175),"") in the first cell then =IF(D8>0,(E7-D8),"") in the cell next to it. If there is no data in the cell it displays #VALUE!. Can I get this to display nothing if there's no value? I tried to attach workbook but it said invalid file?
 
That's correct, "" isn't 0. Try

Code:
 =if(or(D8>0,D8<>""),E7-D8,"")

The OR evaluates two or more conditions and returns true if any of the conditions are met.
 
Ok, I've managed to attach it, I just had to delete the x from the .xlsx extension as it wouldn't let me upload it. I'm having problems with the remainder column, I need that to not display anything unless the condition is true and it performs the calculation, also I need the total box to work correctly with the new formulas. If anyone could help I'd appreciate it.

Thanks
View attachment Productive.xls
 
Last edited:
That's correct, "" isn't 0. Try

Code:
 =if(or(D8>0,D8<>""),E7-D8,"")

The OR evaluates two or more conditions and returns true if any of the conditions are met.

I tried this equation but it still displays #VALUE! in the box instead of nothing. Any idea how I could do that?

Thanks
 
Ta-dah! I used the isnumber function to get around blank values.
 
Last edited:
PS I forgot the #value!s at the bottom of the sheet, I guess they just need to be sums.
 
Ah that's great, you're amazing thanks! Just wish I could add to your reputation again!! I'll have to remember that Isnumber function, it'll definitly come in handy again.
 
Last edited:
No worries, always glad to help - beats working :)
 

Users who are viewing this thread

Back
Top Bottom