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
32179

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

=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
32178

That's correct, "" isn't 0. Try

=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.

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.

No worries, always glad to help - beats working :)

Lol, yes i guess it's good to have something to break up the day!