Zero Columns and formulas

[darcyfournier]

I know how to do the following- just putting it here for reference. Would love to know how to cut and paste a section of excel to show you...but this will have to do.

A1: Market Rent
B1: Current Rent
C1: % Difference Between
D1: New Proposed Rent
E1: Percentage increase between D1 and b1
F1: Accepted New Rate
G1: Percentage increase between F1 and b1
H1: Dollar Increase between F1 and b1

Problem...sometimes F1 will be zero because the tenant chooses to move...so my cell for G1 shows -100% and my cell for H1 shows the - dollar amount. This messes with my over all totals.

If a cell in zero, how to I exclude those cells from being calculated by the overall formula. How do I get formula's to ignore zero or empty cells

Does this make sense? I did the best I could to describe it.

 

[petertenthije]

You can use this in cells G1 and H1

=if(or($F1="",$F1=0),"",formula)

 

[darcyfournier]

You can use this in cells G1 and H1

=if(or($F1="",$F1=0),"",formula)

Thank you

It worked for G1 and H1 but when I extended it down to all other cells (g2, h2, g3, h3 etc.) it did not work. It put #name in the cell instead of leaving it blank.

I would like to see it show $0.00

 

[petertenthije]

It worked for G1 and H1 but when I extended it down to all other cells (g2, h2, g3, h3 etc.) it did not work. It put #name in the cell instead of leaving it blank.

That's odd, I would expect it to either fail everywhere, or work everywhere. Could you copy the formula in this thread?

I would like to see it show $0.00

Replace the "" with a 0:
=if(or($F1="",$F1=0),0,formula)

 

[darcyfournier]

Example 1

J3= $0
K3=$1050

L3 is showing 0%
formula used =IF(OR($J3=0,$L3=0),0,J3/K3)-1

So that works...this is what happens to the next set when I carry the formula down the rows

J4=$1000
K4=$1050

L4 shows 0% when is should show a percentage increase
=IF(OR($J4=0,$L4=0),0,J4/K4)-1

 

[petertenthije]

In the formula, should L4 not be J4? Otherwise you get cross-reference error.
Note also the addition of two brackets to avoid answer -1 when there should be 0.

=IF(OR($J4=0,$J4=""),0,(J4/K4)-1)