If Function with 3 arguments

C

CIW

I am trying to enter an IF function that has 3 separate arguments:

If C8 is <= to 199,999, then place the value 5,000;
If C8 is between 200,000 and 499,999, then place the value 10,000;
If C8 is => 500,000, then place the value 15,000.

I have not done functions for several years, and cannot remember how to
compose this function--can you please help me?

Carol
 
J

John Bundy

Here you go

=IF(C8<=199999,5000,IF(C8>=200000,IF(C8<500000,10000,IF(C8>=500000,15000,""))))

-John
 
T

T. Valko

Try this:

I assume 0 is not a valid amount?

=LOOKUP(C8,{0,1,200000,500000},{"",5000,10000,15000})

Or, the IF version:

=IF(C8>=500000,15000,IF(C8>=200000,10000,IF(C8>0,5000,"")))

Biff
 
A

Arvi Laanemets

Hi

=5000*MATCH(A1,{0;200;500},1)

When you want include negative values too return 5000, then
=5000*MATCH(A1,{-999999999;200;500},1)
 
B

bplumhoff

Hello,

maybe some test values help you to decide which formula to take:

macropod Biff_1 Biff_2 Avri_mod Bernd
-1 5000 #N/A #N/A 5000
199999 5000 5000 5000 5000 5000
199999.99 10000 5000 5000 5000 5000
200000 10000 10000 10000 10000 10000
499999 10000 10000 10000 10000 10000
499999.99 15000 10000 10000 10000 10000
500000 15000 15000 15000 15000 15000
1.00E+300 15000 15000 15000 15000 15000

macropod =IF(C8<=199999,5000,IF(C8<=499999,10000,15000))
Biff_1 =LOOKUP(C8,{0,1,200000,500000},{"",5000,10000,15000})
Biff_2 =IF(C8>=500000,15000,IF(C8>=200000,10000,IF(C8>0,5000,"")))
Avri_mod =5000*MATCH(C8/1000,{0;200;500},1)
Bernd =LOOKUP(C8,{-1E+300,200000,500000},{5000,10000,15000})

Regards,
Bernd
 
Top