Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 ;) |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 ;) |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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)) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
IF formula? | Excel Worksheet Functions | |||
Formula works in some cells, doesn't in other | New Users to Excel | |||
Formula checking multiple worksheets | Excel Worksheet Functions | |||
Problem with VBA returning the contents of a long formula. | Excel Discussion (Misc queries) | |||
Match / Vlookup within an Array formula | Excel Discussion (Misc queries) |