Excel code to sum different prices depending on the range the price is located.

**Uriakus** · 06-04-2012, 12:33 AM

I hope you can help me... Excuse my english
I need to create a formula to calculate multiple ranges of prices.
Let me give you and example... The answer must be shown in column B

If the value is between 0 - 99999 i need it to sum 28000 to the column A and show it in column B
If the value is between 100000 - 199999 i need it to sum 38000 to the column A
If the value is between 200000 - 249999 i need it to sum 48000 to the column A
If the value is between 250000 - 499999 i need it to sum 58000 to the column A
If the value is between 500000 - 749999 i need it to sum 68000 to the column A
If the value is between 750000 - 999999 i need it to sum 78000 to the column A
and so on.

A -------------------------- B
96000
36000
128000
356000
458000

Thank in advance.

**jraj1106** · 06-04-2012, 01:29 AM

Hi

The formula for this is:

=IF(AND(A1>0,A1<=99999),SUM(A1,28000),IF(AND(A1>=100000,A1<=199999),SUM(A1,38000),IF(AND(A1>=200000,A1<=249999),SUM(A1,48000),IF(AND(A1>=250000,A1<=499999),SUM(A1,58000),IF(AND(A1>=500000,A1<=749999),SUM(A1,68000),SUM(A1,78000))))))

**Cutter** · 06-04-2012, 06:53 AM

It looks like you can use something like:
=18000+10000*LOOKUP(A2,{0,10000,20000,25000,50000,75000},{1,2,3,4,5,6})

or even

=18000+10000*LOOKUP(A2,{0,1,2,2.5,5,7.5}*10000,{1,2,3,4,5,6}) where A2 holds the raw value

Excel code to sum different prices depending on the range the price is located.

LinkBack

Thread Tools

Rate This Thread

Display

Excel code to sum different prices depending on the range the price is located.

Re: Excel code to sum different prices depending on the range the price is located.

Re: Excel code to sum different prices depending on the range the price is located.

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions