Formula to calculate difference between values.

[temiomadeli]

Hi all,

I need a formula that will allow me to help calculate the difference between a value and the next value upwards. so for example as per the table below Megans quarterly billings are 12,458, i need a formula that will calculate the difference between this value and the nearest value up which is 20,000. I need to be able to drag the formula down for other names that follow. Please help

Billing Threshold
0	10000
10000	20000
20000	35000
35000	50000
50000
Consultants	Quarterly billings
Megan	12,458
Cheryl	28,451
Alicia	54,851

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

 

[gaz_chops]

Try

=LOOKUP(B8,$A$2:$B$6)

 

[AlanY]

i've added the 75000 to make kit works

Excel 2012
	A	B	C	D	E
1	0		Consultants	Quarterly billings
2	10000		Megan	12,458	20000
3	20000		Cheryl	28,451	35000
4	35000		Alicia	54,851	75000
5	50000
6	75000
Sheet1

Excel 2012
	E
2	20000
Sheet1

Cell Formulas
Range	Formula
E2	=INDEX($A$2:$A$6,MATCH(D2,$A$1:$A$6))

 

[RCBricker]

try this.

=SUM(LOOKUP(B12,$A$2:$B$6)-B12)

rich

 

[temiomadeli]

Try

=LOOKUP(B8,$A$2:$B$6)[/QUOTE

Sorry but i will need it to subtract from the next value upwards so for example Megan's will be 20,000 - 12,458 = 7542

 

[temiomadeli]

i've added the 75000 to make kit works

Excel 2012

	A	B	C	D	E
1	0		Consultants	Quarterly billings
2	10000		Megan	12,458	20000
3	20000		Cheryl	28,451	35000
4	35000		Alicia	54,851	75000
5	50000
6	75000

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

Sheet1​

Excel 2012

	E
2	20000

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

Sheet1​

Worksheet Formulas Cell Formula E2 =INDEX($A$2:$A$6,MATCH(D2,$A$1:$A$6)) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

same again. Sorry but i will need it to subtract from the next value upwards so for example Megan's will be 20,000 - 12,458 = 7542

 

[temiomadeli]

try this.

=SUM(LOOKUP(B12,$A$2:$B$6)-B12)

rich

Hopefully, there's a simper formula but i will try this, thanks for your help

 

[gaz_chops]

Then simply edit to be

=LOOKUP(B8,$A$2:$B$6)-B8

[/FONT][/COLOR][TABLE="width: 195"]
<colgroup><col span="3"></colgroup><tbody>[TR]
[TD="colspan: 2"]Billing Threshold[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]0[/TD]
[TD="align: right"]10000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]10000[/TD]
[TD="align: right"]20000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]20000[/TD]
[TD="align: right"]35000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]35000[/TD]
[TD="align: right"]50000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]50000[/TD]
[TD="align: right"]60000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Consultants[/TD]
[TD="colspan: 2"]Quarterly billings[/TD]
[/TR]
[TR]
[TD]Megan[/TD]
[TD="align: right"]12,458[/TD]
[TD="align: right"]7,542[/TD]
[/TR]
[TR]
[TD]Cheryl[/TD]
[TD="align: right"]28,451[/TD]
[TD="align: right"]6,549[/TD]
[/TR]
[TR]
[TD]Alicia[/TD]
[TD="align: right"]54,851[/TD]
[TD="align: right"]5,149[/TD]
[/TR]
</tbody>[/TABLE]
[COLOR=#333333][FONT=Lucida Grande]

 

[RCBricker]

i've added the 75000 to make kit works

Excel 2012
	A	B	C	D	E
1	0		Consultants	Quarterly billings
2	10000		Megan	12,458	20000
3	20000		Cheryl	28,451	35000
4	35000		Alicia	54,851	75000
5	50000
6	75000
Sheet1

Excel 2012
	E
2	20000
Sheet1

Cell Formulas
Range	Formula
E2	=INDEX($A$2:$A$6,MATCH(D2,$A$1:$A$6))

hey Alan how do you do this with the tables?

I find it very easy to read and think it would be easier when answering formula questions

thanks,

rich

 

[gaz_chops]

hey Alan how do you do this with the tables?

I find it very easy to read and think it would be easier when answering formula questions

thanks,

rich

Check my signature for how to.