Excel formula to return value based on cell value within a range

0
votes

Screenshot of data

I need excel to sum user amount and price based on amount of users. However the pricing is staggered and the price per user is cheaper the more users you have

Users Price per user

1-10 600

11-20 500

21-30 400

31-40 300

41-50 200

50+ 100

Is it possible to have a formula which returns a value based on the number of users but uses the price depending on which pricing bracket the amount of users falls into

1
excelformula
Can you add an example of what you want to see as result ? It's difficult to understand by the text – Mikku
Though there is a function called Offset which might be of some use to get the value from adjacent cell. – Mikku
So for example if I entered 20 into a cell it would return 10000 (20*500) or if I entered 35 it would return 10500 (35*300) – Zoki
Can you also paste a Screenshot of the Data that you have provided ? – Mikku
Added a screenshot in original post. Hope it makes sense. Thanks! – Zoki

1 Answers

2
votes

Is there a finite amount of users or could it be unlimited?

There are a couple of formula methods you could use but could become cumbersome if we are talking 20+ potential brackets.

  1. Compound IF statement:

Lets say A1 is where the number of users is entered Col B is where your lower brackets are held Col C is where your higher brackets are held Col D = price

=IF(AND($A$1>$B1,$A$1<=$C1),($A$1*$D1),IF(AND($A$1>$B2,$A$1<=$C2,($A$1*$D2).....,"") etc
  1. Create your brackets on a separate sheet and use a VLOOKUP to identify which bracket the user number lies within and multiple by the resulting bracket price.