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

[TABLE="width: 582"]
<colgroup><col><col><col></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] [/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="colspan: 3"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Consultants[/TD]
[TD] Quarterly billings[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Megan[/TD]
[TD] 12,458[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Cheryl[/TD]
[TD] 28,451[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Alicia[/TD]
[TD] 54,851[/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]

 

[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
Consultants	Quarterly billings
Megan
Cheryl
Alicia

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]10000[/TD]
[TD="align: right"][/TD]

[TD="align: right"]12,458[/TD]
[TD="bgcolor: #FFFF00, align: right"]20000[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]20000[/TD]
[TD="align: right"][/TD]

[TD="align: right"]28,451[/TD]
[TD="align: right"]35000[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]35000[/TD]
[TD="align: right"][/TD]

[TD="align: right"]54,851[/TD]
[TD="align: right"]75000[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]50000[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]75000[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet1​

Excel 2012

	E

<colgroup><col style="width: 25pxpx"><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]2[/TD]
[TD="bgcolor: #FFFF00, align: right"]20000[/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]E2[/TH]
[TD="align: left"]=INDEX($A$2:$A$6,MATCH(D2,$A$1:$A$6))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

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.