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]

 

[AlanY]

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.

oops, the secret is out

 

[temiomadeli]

Then simply edit to be

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

[TABLE="width: 195"]
<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]

big thank you to you and the other chaps. you guys really are incredible