formula help

[Guest]

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 C>0 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

 

[Guest]

TTT... help please

 

[Vito]

Try:


=IF(H1=0,0,IF((C1-B1)>H1,H1,(B1+H1)-B1))

 

[Guest]

Worked perfectly thanks!!

 

[Dana DeLouis]

Worked perfectly thanks!!

=IF(H1=0,0,IF((C1-B1)>H1,H1,(B1+H1)-B1))

Hi. Just two cents. Note that ",(B1+H1)-B1" reduces to just H1.
Therefore, the answer is H1 no matter what C1-B1 is.
Looks to me that the above equation could be reduced to:
=IF(H1=0,0,H1)
but could be reduced further to just:
=H1.

Hope I understood this correctly.

 

[Vito]

Hi. Just two cents. Note that ",(B1+H1)-B1" reduces to just H1.
Therefore, the answer is H1 no matter what C1-B1 is.
Looks to me that the above equation could be reduced to:
=IF(H1=0,0,H1)
but could be reduced further to just:
=H1.

Hope I understood this correctly.

Good Point Dana...didn't look at the details.

 

[Guest]

Dana,

I totally appreciate the input, but the formula you gave me only partially
resolves the query. Essentially I have layed out a spreadsheet that
calculates how many cans(as we call them) or the cargo containers of 18
wheelers are being used(column B) and from where... we have our own
cans(column C), but unfortuantely some of them are just NOT used for no
reason in particular, and we end up having to pay another company to use
their cans(column H)(which we believe is only because they are closer at the
time of loading)... So, the formula that is needed is to determine how many
cans we actually have in inventory, how many we need to use from inventory,
how many are used from the other company, so the end result needed is to
determine how many of these cans we used that we shouldn't have.


The 'detailed problem' is outlined underneath.

COLUMN B = CANS LOADED
COLUMN C = CANS AVAILABLE
COLUMN H = EXPENSIVE ALTERNATIVE AVAILABLE
COLUMN 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 C>0 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 column R...

CONDITION
=if(h1=0,0,(c1-b1))

ELSE

=if((C1-B1)=>H1,H1,((B1+H1)-B1))