DisFormula to calculate discount amount base on the value and percentages.

[asyamonique]

3.000 - 5.000 ............ %10

5.000 - 10.000 .......... %15

10.000 - 20.000 ........ %20

20.000 - 100.000 ......... %25

100.000 and more ....... %30

<colgroup><col></colgroup><tbody>
</tbody>

Good Day,
I have values all the way down in one column.
Is it possible to put formula on next column which will give the results if the values reaches in the limits of given table.
Sample:
Any value in the column if 4.000, next column value will be 400
Or any value in the column if 25.000, next column value will be 6.250

Many Thanks

 

[Marcílio_Lobão]

asyamonique, Good morning.

Suppose:

A1 --> VALUE

B1 --> FORMULA -->

=A1 * LOOKUP(A1, {0,3,5,10,20,100}*1000, {0,0.1,0.15,0.2,0.25,0.3})

Please, tell us if it worked as you want.

I hope it helps.

 

[oldbrewer]

3.000 - 5.000 ............ %10											this table is named mytable
5.000 - 10.000 .......... %15											0	0
10.000 - 20.000 ........ %20											3000	10
20.000 - 100.000 ......... %25											5000	15
100.000 and more ....... %30											10000	20
											20000	25
											100000	30
Good Day,											9999999	30
I have values all the way down in one column.
Is it possible to put formula on next column which will give the results if the values reaches in the limits of given table.
Sample:
Any value in the column if 4.000, next column value will be 400
Or any value in the column if 25.000, next column value will be 6.250
					col F
			row 16		2000	0	#####
					4000	400
					7000	1050
					11000	2200
					19000	3800
					21000	5250
					77000	19250
					99000	24750
					105000	31500
					777777	233333.1
			###
			=F16*VLOOKUP(F16,mytable,2)/100

<colgroup><col><col span="14"></colgroup><tbody>
</tbody>

 

[MartyS]

Excel 2010

	A	B	C	D	E	F	G	H
1	Numbers	Results			Bin	Percentage		Percentage Matched
2	£2,468.00	£0.00			3000	0.1		0
3	£2,769.00	£0.00			5000	0.15		0
4	£3,334.00	£333.40			10000	0.2		0.1
5	£4,997.00	£499.70			20000	0.25		0.1
6	£5,077.00	£761.55			100000	0.3		0.15
7	£9,976.00	£1,496.40						0.15
8	£10,338.00	£2,067.60						0.2
9	£19,835.00	£3,967.00						0.2
10	£20,055.00	£5,013.75						0.25
11	£99,886.00	£24,971.50						0.25
12	£100,113.00	£30,033.90						0.3

<tbody>
</tbody>

​

Array Formulas Cell Formula B2:B12 {=IFERROR(LOOKUP($A$2:$A$12,$E$2:$E$6,$F$2:$F$6),0)*$A$2:$A12} H2:H12 {=IFERROR(LOOKUP(A2:$A$12,$E$2:$E$6,$F$2:$F$6),0)} <thead> </thead><tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

The [Percentage Matched] column is unnecessary - I just added it so you could see the Array Results being used and then multiplied by column A.

Note: Array Formula - requires the range to be selected, then CTRL+SHIFT+ENTER to enter.

 

[asyamonique]

-6,000	900
-3,000	300
-4,000	400
16,000	0
-23,000	5,750
12,000	0

<tbody>
</tbody>

Many Thanks Marcilio and others,
Is there any way if only the value is negative the percentage will appear if positive it will remain zero?
Thanks again.

 

[markmzz]

-6,000	900
-3,000	300
-4,000	400
16,000	0
-23,000	5,750
12,000	0

<tbody>
</tbody>

Many Thanks Marcilio and others,
Is there any way if only the value is negative the percentage will appear if positive it will remain zero?
Thanks again.

Try this:

Formula01 - in G16 and copy down: =LOOKUP(F16,myTable)*F16

Formula02 - in G28 and copy down: =LOOKUP(ABS(F28),myTable)*(F28<0)*ABS(F28)

	E	F	G	H	I	J
14
15		Data	Formula01		myTable
16		2000	0		0	0%
17		4000	400		3000	10%
18		7000	1050		5000	15%
19		11000	2200		10000	20%
20		19000	3800		20000	25%
21		21000	5250		100000	30%
22		77000	19250
23		99000	24750
24		105000	31500
25		777777	233333,1
26
27		Data	Formula02
28		-6000	900
29		-3000	300
30		-4000	400
31		16000	0
32		-23000	5750
33		12000	0
****	**	********	**********	**	*******	****

<tbody>
</tbody>

Markmzz

 

[MartyS]

-6,000	900
-3,000	300
-4,000	400
16,000	0
-23,000	5,750
12,000	0

<tbody>
</tbody>

Many Thanks Marcilio and others,
Is there any way if only the value is negative the percentage will appear if positive it will remain zero?
Thanks again.

Excel 2010

	A	B	C	D	E	F
1	Numbers	Results			Bin	Percentage
2	£2,468.00	£0.00			3000	0.1
3	-£2,769.00	£0.00			5000	0.15
4	£3,334.00	£0.00			10000	0.2
5	-£4,997.00	£499.70			20000	0.25
6	£5,077.00	£0.00			100000	0.3
7	-£9,976.00	£1,496.40
8	£10,338.00	£0.00
9	-£19,835.00	£3,967.00
10	£20,055.00	£0.00
11	-£99,886.00	£24,971.50
12	£100,113.00	£0.00

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

Array Formulas Cell Formula B2:B12 {=IF(SIGN($A$2:$A$12)=-1,IFERROR(LOOKUP(ABS($A$2:$A$12),$E$2:$E$6,$F$2:$F$6),0)*ABS($A$2:$A12),0)} <thead> </thead><tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

 

[Marcílio_Lobão]

asyamonique, Good morning.
Suppose:

A1 --> VALUE

B1 --> NEW FORMULA -->

=IF(A1="", "", LOOKUP(ABS(A1), {0,3,5,10,20,100}*1000, {0,0.1,0.15,0.2,0.25,0.3}) * (A1<0) * (ABS(A1)))

Please, tell us if it's working as you need.

I hope it helps.

 

[asyamonique]

Many Thanks to all