Combining two formulas in to one

[Joeclupis]

Hello all,
I have a worksheet that compares volume of product sold year to year by day. It calculates a a delta between the two amounts. I have two formulas to be used depending on whethere or not there is a leading zero in the comparison set.
#1 ) =IF(OR(B183=0,C183=0),1,(C183-B183)/B183) -- used if there is a zero (0) in column B
#2 ) =IF(AND(B182=0,(C182=0)),1,(C182-B182)/B182) -- used if there is a positive integer in column B

My question: is there a way to combine both formulas into one cohesive formula so that I don't have to physically change the formula based on column B?

Thank you

Joseph Carney, CM
Operations Specialist

 

[Special-K99]

Your statements contradict one another.

I have two formulas to be used depending on whethere or not there is a leading zero in the comparison set.
#1 ) =IF(OR(B183=0,C183=0),1,(C183-B183)/B183) -- used if there is a zero (0) in column B
#2 ) =IF(AND(B182=0,(C182=0)),1,(C182-B182)/B182) -- used if there is a positive integer in column B

These formulas check for EQUALS ZERO.
So which is it you're after, EQUALS ZERO or LEADING ZERO ?

FYI

(C183-B183) / B183 = C183/B183 - 1

 

[Joeclupis]

Special-K99,

Evidently I am stating this in a way to confuse. What I had to do to get the formulas to output a percentage was to work it as I showed in the formulas. What I want to do is to get a percentage change between the two columns. Using Column B as the base. Sometimes there are zeros in either or both columns. Obviously, when trying to figure a percentage using a zero will give you a error. Hence, the way that the formula are written is if there is a zero, then the return is 1 (100%), if there are positive integers then the formula will execute the calculation and give a percentage.

What I would like to be able to do is to consolidate both formulas into one so that I can paste it into all cells, not have to paste certain formulas in to certain cells.

 

[shg]

How about just =IFERROR(C2/B2 - 1, 1)

 

[handysmurf]

err the first one enters 1 if either of the cells are zero the seconds enters 1 if both the cells are zero. I'm not understanding why you need the second one at all.

 

[Joeclupis]

shg -- Thanks -that got it. Now that I see it, I understand the formula.
Handysmurf -- for some reason, when I used the first formula on on cells that the second formula worked on I would get an error. Now that I have the new formula, I can see where I was having issues.

Again, Thanks to everyone who helped!

Joe