Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IF-THEN Formula help needed
I am having trouble finding an IF-THEN formula that works for my scenario. My
$AT5:$AT$34 are numbers 1-30 and trying a list in the IF-THEN formula did not work. I also tried entering each number separately 1, 2, 3, 4, 5 etc... but that did not work (too many IF-THEN statements). Below is my formula: =IF(G5/$F$6=$AT$5:$AT$34,$E$6*G3,0) Essentially if G5/$F$6 returns a whole number or integer (without decimels), then I want the calculation of $E$6*G3 to happen. Does anyone know how to make the IF-THEN statement work with a list in a single row or column, or have a better idea? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IF-THEN Formula help needed
Brandty123 Wrote: I am having trouble finding an IF-THEN formula that works for my scenario. My $AT5:$AT$34 are numbers 1-30 and trying a list in the IF-THEN formula did not work. I also tried entering each number separately 1, 2, 3, 4, 5 etc... but that did not work (too many IF-THEN statements). Below is my formula: =IF(G5/$F$6=$AT$5:$AT$34,$E$6*G3,0) Essentially if G5/$F$6 returns a whole number or integer (without decimels), then I want the calculation of $E$6*G3 to happen. Does anyone know how to make the IF-THEN statement work with a list in a single row or column, or have a better idea? If I'm not mistaken, you'd be better off using the MOD function. ie. =IF(AND(MOD(G5,$F$6)=0,G50),$E$6*G3,0) If you need it limited to only the first 30 multiples, you can modify it slightly: =IF(AND(MOD(G5,$F$6)=0,G50,G5<=$F$6*30),$E$6*G3,0 ) Scott -- Maistrye ------------------------------------------------------------------------ Maistrye's Profile: http://www.excelforum.com/member.php...o&userid=36078 View this thread: http://www.excelforum.com/showthread...hreadid=558930 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IF-THEN Formula help needed
=if( int(g5/$f$6)=g5/$f$6)$e$6*g3,0) or to include the 1 to 30 conditions if( and(int(g5/$f$6)=g5/$f$6,g5/$f$6=0,g5/$f$6<=30),$e$6*g3,0) or in case f6 is 0 if( and($f$6<0,int(g5/$f$6)=g5/$f$6,g5/$f$6=0,g5/$f$6<=30),$e$6*g3,0) Regards Dav -- Dav ------------------------------------------------------------------------ Dav's Profile: http://www.excelforum.com/member.php...o&userid=27107 View this thread: http://www.excelforum.com/showthread...hreadid=558930 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IF-THEN Formula help needed
Another way, =IF(ISNUMBER(MATCH(G5/$F$6,$AT$5:$AT$34,0)),$E$6*G3,0) HTH Steve -- SteveG ------------------------------------------------------------------------ SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571 View this thread: http://www.excelforum.com/showthread...hreadid=558930 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IF-THEN Formula help needed
Try this:
=(COUNTIF(AT5:AT34,G5/F6)0)*(E6*G3) -- HTH, RD ================================================== === Please keep all correspondence within the Group, so all may benefit! ================================================== === "Brandty123" wrote in message ... I am having trouble finding an IF-THEN formula that works for my scenario. My $AT5:$AT$34 are numbers 1-30 and trying a list in the IF-THEN formula did not work. I also tried entering each number separately 1, 2, 3, 4, 5 etc... but that did not work (too many IF-THEN statements). Below is my formula: =IF(G5/$F$6=$AT$5:$AT$34,$E$6*G3,0) Essentially if G5/$F$6 returns a whole number or integer (without decimels), then I want the calculation of $E$6*G3 to happen. Does anyone know how to make the IF-THEN statement work with a list in a single row or column, or have a better idea? |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IF-THEN Formula help needed
That did it. Thank you for your help!!!
"RagDyeR" wrote: Try this: =(COUNTIF(AT5:AT34,G5/F6)0)*(E6*G3) -- HTH, RD ================================================== === Please keep all correspondence within the Group, so all may benefit! ================================================== === "Brandty123" wrote in message ... I am having trouble finding an IF-THEN formula that works for my scenario. My $AT5:$AT$34 are numbers 1-30 and trying a list in the IF-THEN formula did not work. I also tried entering each number separately 1, 2, 3, 4, 5 etc... but that did not work (too many IF-THEN statements). Below is my formula: =IF(G5/$F$6=$AT$5:$AT$34,$E$6*G3,0) Essentially if G5/$F$6 returns a whole number or integer (without decimels), then I want the calculation of $E$6*G3 to happen. Does anyone know how to make the IF-THEN statement work with a list in a single row or column, or have a better idea? |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IF-THEN Formula help needed
Just for the heck of it, I modified your formula. I made it into an array
formula so that the OP would not have to have the numbers 1 - 30 in the spreadsheet itself. =IF(ISNUMBER(MATCH(G5/$F$6,ROW(INDIRECT("1:30")),0)),$E$6*G3,0) Usual caveat with array formulas: Must be entered using ctrl-shift-enter -- Kevin Vaughn "SteveG" wrote: Another way, =IF(ISNUMBER(MATCH(G5/$F$6,$AT$5:$AT$34,0)),$E$6*G3,0) HTH Steve -- SteveG ------------------------------------------------------------------------ SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571 View this thread: http://www.excelforum.com/showthread...hreadid=558930 |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IF-THEN Formula help needed
Appreciate the feed-back.
-- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Brandty123" wrote in message ... That did it. Thank you for your help!!! "RagDyeR" wrote: Try this: =(COUNTIF(AT5:AT34,G5/F6)0)*(E6*G3) -- HTH, RD ================================================== === Please keep all correspondence within the Group, so all may benefit! ================================================== === "Brandty123" wrote in message ... I am having trouble finding an IF-THEN formula that works for my scenario. My $AT5:$AT$34 are numbers 1-30 and trying a list in the IF-THEN formula did not work. I also tried entering each number separately 1, 2, 3, 4, 5 etc... but that did not work (too many IF-THEN statements). Below is my formula: =IF(G5/$F$6=$AT$5:$AT$34,$E$6*G3,0) Essentially if G5/$F$6 returns a whole number or integer (without decimels), then I want the calculation of $E$6*G3 to happen. Does anyone know how to make the IF-THEN statement work with a list in a single row or column, or have a better idea? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
continuous sum formula needed | Excel Discussion (Misc queries) | |||
Formula Help Needed | Excel Discussion (Misc queries) | |||
More Help Needed with Count formula | Excel Worksheet Functions | |||
Formula checking multiple worksheets | Excel Worksheet Functions | |||
Help! Formula needed. | Excel Discussion (Misc queries) |