IF-THEN Formula help needed

[Brandty123]

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

Scott

[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

Dav

[SteveG]

Another way,

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

HTH

Steve

[RagDyeR]

Try this:

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

--

HTH,

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

"Brandty123" <[email protected]> wrote in message
news:[email protected]...
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?

[Brandty123]

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" <[email protected]> wrote in message
> news:[email protected]...
> 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?
>
>
>
>

[Kevin Vaughn]

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

[Ragdyer]

Appreciate the feed-back.
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Brandty123" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
>> news:[email protected]...
>> 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?
>>
>>
>>
>>