Dont show content if cell has a number higher than 1

[MetrOslo]

Hello,

I have a formula that is working fine: =D9/0,6

But I want this formula to display nothing IF the number of another cell is higher than 1.

Can anyone help me?

Jon

 

[MetrOslo]

=f9*c9

 

[AlanY]

one trick you can try is to use conditional formatting, use the same font/background colour if Cell is <1

 

[Rick Rothstein]

How can I use the content of this cell now? If its hidden I can not use it and I get an error on the next cell. Because the next cell uses thisone to demonstrate the content..

So, if I am reading what you have written in this thread so far, you want to calculate a value in a cell so that you can use it in other calculations, but you do not want anyone to see what that value is if a different cell is greater than one, is that correct? If so, you would need to use Conditional Formatting on the cell with your original formula (not the variation that Joe gave you)... the condition would check if the other cell is greater than 1 and, if so, set the conditional Custom Number Format to ;;; (that is three semi-colons next to each other).

 

[AlanY]

So, if I am reading what you have written in this thread so far, you want to calculate a value in a cell so that you can use it in other calculations, but you do not want anyone to see what that value is if a different cell is greater than one, is that correct? If so, you would need to use Conditional Formatting on the cell with your original formula (not the variation that Joe gave you)... the condition would check if the other cell is greater than 1 and, if so, set the conditional Custom Number Format to ;;; (that is three semi-colons next to each other).

interesting, not quite sure what's the Custom Number Format to ;;; doing. Try it and it works for ;;; and ;; and ; as well, even with NOTHING on the format condition!?

 

[Rick Rothstein]

interesting, not quite sure what's the Custom Number Format to ;;; doing. Try it and it works for ;;; and ;; and ; as well, even with NOTHING on the format condition!?

I cannot make no semi-colons work... the Conditional Formatting format box says "No Format Set" when I try it. If you put one semi-colon in the Type field for the Custom Format, the next time you look at that cell's Conditional Custom Format, I think you will see Excel has changed it to two semi-colons automatically. Two semi-colons define three conditions... positive number, negative numbers and zeroes... putting nothing in those field means nothing is displayed for those type of values. The third semi-colon handles text and putting nothing for it also means nothing would be display if the formula results in a text display (which, in this case, is not possible and, so, could be omitted).

 

[AlanY]

I cannot make no semi-colons work... the Conditional Formatting format box says "No Format Set" when I try it. If you put one semi-colon in the Type field for the Custom Format, the next time you look at that cell's Conditional Custom Format, I think you will see Excel has changed it to two semi-colons automatically. Two semi-colons define three conditions... positive number, negative numbers and zeroes... putting nothing in those field means nothing is displayed for those type of values. The third semi-colon handles text and putting nothing for it also means nothing would be display if the formula results in a text display (which, in this case, is not possible and, so, could be omitted).

Rick,

Thanks very much for your explanations and you're spotted on regarding Excel changed it back to 2 semi-colon.

Alan