Cant get this formula right

[nutritiouspig]

What I am trying to do is set up a formula on excel to select a value from a cell and if that value is less than $50,000,000, input 8%, if it is between $50,000,001 and $149,999,999, input 5% and if it is over $150,000,000, input 3%.

Any Help Appreciated.

Thanks!
S.

 

[Yogi Anand]

Hi simfonik:

Let us tighten up the spec a bit ... what should happen if the value in the cell is = 50000000 ?

 

[jcvoth]

test timer.xls
	A	B	C	D
7	testnum	result
8	49000000	0.08
9	50000001	0.05
10	150000001	0.03
Sheet1

I assumed a couple of things you didn't specify here. notably, if the result is equal to 50M, 150M, etc. Please let me know if this is not what you intended.

-Jarrod

 

[SydneyGeek]

Assuming that you only hav the 3 options, an IF calculation will do the job.

For values in Column B, with the percent to go into Column C (starting at C2), try:

=IF(B2>150000000,3%,if(B2>50000000,5%,8%))

Fill down the column to complete the job.

Denis

 

[PaddyD]

usual solution is with vlookup:

Book1
	A	B	C	D	E
1	MinimumofRange	RangeValue		Find	Return
2	0	0		0	0
3	1	1		1	1
4	10	2		9	1
5	50	3		10	2
6	100	4		11	2
7				49	2
8				50	3
9				51	3
10				100	4
11				101	4
Sheet1

...in general, use the minimum value in the range as the key for the whole range...

 

[nutritiouspig]

Thanks SidneyGeek, that worked great!