formula help
CELL B = CANS LOADED
CELL C = CANS AVAILABLE
CELL H = EXPENSIVE ALTERNATIVE AVAILABLE
CELL R = INEFFECTIVE COUNT
ESSENTIALLY THE FORMULA IN WORDS IS:
IF C=B THEN B SHOULD BE TAKEN FROM C = EFFECTIVE <-- IN A PERFECT 'WORLD'
THIS WOULD BE 100% EFFECTIVE
HOWEVER, SINCE 'THE WORLD' IS NOT PERFECT SO...
IF B COMES FROM H, BUT C0 THEN CALCULATE
CELL R(HOW MANY HAVE BEEN TAKEN IF C=1
EXAMPLE:
B=1
C=14
H=1
R SHOULD = 1 BECAUSE C HAD 14 AVAILABLE BUT THEY WERE TAKEN FROM H
THIS IS WHAT I CAME UP WITH FOR THE CELL R FORMULA WITH LIMITED EXCEL
KNOWLEDGE, I KNOW THAT THIS WILL NOT WORK... SO THESE 'TWO IFs' need
to be put together into one working formula for cell r...
CONDITION
=if(h1=0,0,(c1-b1))
ELSE
=if((C1-B1)=H1,H1,((B1+H1)-B1))
thanks in advance for any input and sorry for the caps, was not yelling ;)
|