Solved if multiple condition

[hatmak]

hi all

how do this formula?

if multiple condition

if number less than 2000 ,2000

from 2001 to 3000 -------> result 75% with minimum 2000 and maximum 2250


from 3001 to 4000 -------> result 70% with minimum 2250 and maximum 2800

from 4001 to 5000 -------> result 65 % with minimum 2800 and maximum 3250

over 5000 -------> result 60 % with minimum 3250 and maximum 3500

 

[June7]

If input is between 2001 and 3000, calculate 75% but must be at least 2000 and not exceed 2250?

75% of 3000 is 2250 so the calc will never exceed but 75% of 2001 is 1500.75.

Similar rule for other ranges?

Because of this complexity, I would advise a VBA custom function.

 

[hatmak]

If input is between 2001 and 3000, calculate 75% but must be at least 2000 and not exceed 2250?

75% of 3000 is 2250 so the calc will never exceed but 75% of 2001 is 1500.75.

Similar rule for other ranges?

Because of this complexity, I would advise a VBA custom function.

thanks for reply
but I Try it

=IF(S3<=2000,S3,
(IF(S3<=3000,IF(S3*75/100<2000,2250,S3*75/100),(IF(S3>=4000,IF(S3*70/100<2250,S3*70/100,2800),
(IF(S3<=5000,IF(S3*65/100<2800,3250,S3*65/100),(IF(S3<=5000,IF(S3*60/100<3250,3250,IF(S3*60/100>3500,3500,S3*60/100)))))))))))

what the wrong with this formula

 

[MajP]

tblRange tblRange

ID	rangeMin	rangeMax	PercentAtt	minVal	MaxVal
1​	2001​	3000​	0.75​	2000​	2250​
2​	3001​	4000​	0.7​	2250​	2800​
3​	4001​	5000​	0.65​	2800​	3250​
4​	5001​	99999999​	0.6​	3250​	3500​
5​	-9999999​	1999​	0​	2000​	2000​

SELECT tblvalues.fldvalues,
       tblrange.percentatt,
       [percentatt] * [fldvalues]                              AS ReturnValue,
       tblrange.minval,
       tblrange.maxval,
       Iif([returnvalue] < [minval], [minval],
       Iif([returnvalue] > [maxval], [maxval],
       [returnvalue])) AS FinalValue
FROM   tblrange,
       tblvalues
WHERE  (( ( tblvalues.fldvalues ) >= [rangemin]
          AND ( tblvalues.fldvalues ) <= [rangemax] ));

Query1 Query1

fldValues	PercentAtt	ReturnValue	minVal	MaxVal	FinalValue
100​	0​	0.00​	2000​	2000​	2000​
2002​	0.75​	1501.50​	2000​	2250​	2000​
2999​	0.75​	2249.25​	2000​	2250​	2249.25​
3001​	0.7​	2100.70​	2250​	2800​	2250​
3100​	0.7​	2170.00​	2250​	2800​	2250​
3500​	0.7​	2450.00​	2250​	2800​	2450​
3900​	0.7​	2730.00​	2250​	2800​	2730​
5001​	0.6​	3000.60​	3250​	3500​	3250​

 

[hatmak]

thanks for this soultion

but it required in formula in my project.

can try vlookup or match and index

 

[June7]

Ooops, I just noticed this is Excel, not Access.

 

[MajP]

In excel it is the exact same idea.
1) Build the range table.
2) put min in ascending order as left most column. and no need for the max
3) I would do it seperately and return the same three values (if not this is a pain to write as once)
ReturnValue, MinValue, MaxValue using the vlookup
4) final value is something like
=IF(M2<N2,N2,IF(M2>O2,O2,M2))
where M is the returnvalue, N is min value, O is max value

 

[hatmak]

many thanks for all support

formula solved using this

=CHOOSE(MATCH(A1,{0,2001,3001,4001,5001}),2000,MIN(2250,MAX(2000,75%*A1)),MIN(2800,MAX(2250,70%*A1)),MIN(3250,MAX(2800,65%*A1)),MIN(3500,MAX(3250,60%*A1)))

 

[Gasman]

Would have been polite to mention you had crossposted on both sites?

 

[hatmak]

sorry but said that to add solution for the problem

I removed the link

regards

 

[Gasman]

I will add it. https://www.excelforum.com/excel-general/1410028-if-multiple-condition.html#post5857256