IF-THEN Formula help needed

[Guest]

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?

 

[Maistrye]

I am having trouble finding an IF-THEN formula that works for m
scenario. M
$AT5:$AT$34 are numbers 1-30 and trying a list in the IF-THEN formul
did no
work. I also tried entering each number separately 1, 2, 3, 4, 5 etc..
bu
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 (withou
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 i

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,G5>0),$E$6*G3,0

If you need it limited to only the first 30 multiples, you can modif
it slightly

=IF(AND(MOD(G5,$F$6)=0,G5>0,G5<=$F$6*30),$E$6*G3,0

Scot

 

[Dav]

=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

Da

 

[SteveG]

Another way,

=IF(ISNUMBER(MATCH(G5/$F$6,$AT$5:$AT$34,0)),$E$6*G3,0)

HTH

Stev

 

[RagDyeR]

Try this:

=(COUNTIF(AT5:AT34,G5/F6)>0)*(E6*G3)

--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================

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?

 

[Guest]

That did it. Thank you for your help!!!

 

[Guest]

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

 

[Ragdyer]

Appreciate the feed-back.