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 ;)

formula help
 

TTT... help please :)

"Titanium" wrote:

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 ;)

formula help
 

Try:


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


--
Vito
------------------------------------------------------------------------
Vito's Profile: http://www.excelforum.com/member.php...o&userid=29182
View this thread: http://www.excelforum.com/showthread...hreadid=488549

formula help
 

Worked perfectly thanks!! :)

"Vito" wrote:


Try:


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


--
Vito
------------------------------------------------------------------------
Vito's Profile: http://www.excelforum.com/member.php...o&userid=29182
View this thread: http://www.excelforum.com/showthread...hreadid=488549

formula help
 

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.
--
Dana DeLouis
Win XP & Office 2003


"Titanium" wrote in message
...
Worked perfectly thanks!! :)

"Vito" wrote:


Try:


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


--
Vito
------------------------------------------------------------------------
Vito's Profile:
http://www.excelforum.com/member.php...o&userid=29182
View this thread:
http://www.excelforum.com/showthread...hreadid=488549

formula help
 

Dana DeLouis Wrote:
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.
--
Dana DeLouis
Win XP & Office 2003


"Titanium" wrote in message
...
Worked perfectly thanks!! :)

"Vito" wrote:


Try:


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


--
Vito

------------------------------------------------------------------------
Vito's Profile:
http://www.excelforum.com/member.php...o&userid=29182
View this thread:
http://www.excelforum.com/showthread...hreadid=488549




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


--
Vito
------------------------------------------------------------------------
Vito's Profile: http://www.excelforum.com/member.php...o&userid=29182
View this thread: http://www.excelforum.com/showthread...hreadid=488549

formula help
 

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 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 column R...

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

ELSE

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

"Dana DeLouis" wrote:

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.
--
Dana DeLouis
Win XP & Office 2003


"Titanium" wrote in message
...
Worked perfectly thanks!! :)

"Vito" wrote:


Try:


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