Urgent Need help

[atoubia]

I apologize as I am not an excel vet by any means, but any help would be
greatly appreciated.

Here is a brief overview of what I need to figure out a formula for the
following.
I have a spreadsheet to calculate my cell phone rate plans and usage etc.
where I plug in the number of users on a certain plan and it tells me the
total dollar amount. Here is my delima though. There is a new rate plan that
offers discounts for the first 5 units and then goes back to a set price for
anything 6 or more.
Here are the prices for each unit.
Unit 1 is $99.99 unit 2 $94.99 unit 3 $89.99 unit 4 $84.99 unit 5 $79.99 but
then anything 6 or above goes back to $89.99 so for example if I have a total
of 9 it would look like this

1. $99.99
+
2. $94.99
+
3. $89.99
+
4. $84.99
+
5. $79.99
+
6. $89.99
+
7. $89.99
+
8. $89.99
+
9. $89.99

For a total of $809.91

I just dont how to put this into a formula format. Any help would be
greatly appreciated!

Thanks,
Andrew

 

[Glenn]

I apologize as I am not an excel vet by any means, but any help would be
greatly appreciated.

Here is a brief overview of what I need to figure out a formula for the
following.
I have a spreadsheet to calculate my cell phone rate plans and usage etc.
where I plug in the number of users on a certain plan and it tells me the
total dollar amount. Here is my delima though. There is a new rate plan that
offers discounts for the first 5 units and then goes back to a set price for
anything 6 or more.
Here are the prices for each unit.
Unit 1 is $99.99 unit 2 $94.99 unit 3 $89.99 unit 4 $84.99 unit 5 $79.99 but
then anything 6 or above goes back to $89.99 so for example if I have a total
of 9 it would look like this

1. $99.99
+
2. $94.99
+
3. $89.99
+
4. $84.99
+
5. $79.99
+
6. $89.99
+
7. $89.99
+
8. $89.99
+
9. $89.99

For a total of $809.91

I just dont how to put this into a formula format. Any help would be
greatly appreciated!

Thanks,
Andrew

With number of users in A1:

=A1*89.99+LOOKUP(A1,{1,2,3,4,5},{10,15,15,10,0})

 

[JBeaucaire]

The math is pretty straighfroward. Assume you've entered a number into J33
that has the number of plans, this formula would give you the cost:

=IF(J33<5,LOOKUP(J33,{1,2,3,4},{99.99,194.98,284.97,396.96}),(J33*89.99))

It works out that after the first 4 plans, the price is the same, 89.99 all
the way from the beginning. Someone thought that out, hehe.

Hope this helps.

 

[Don Guillett]

If list of 10 in J1:j10, use this down thru 5 and 89.99 in the rest
=MAX(LARGE(J:J,ROW()),89.99)

 

[atoubia]

Thank you soooo much!

With number of users in A1:

=A1*89.99+LOOKUP(A1,{1,2,3,4,5},{10,15,15,10,0})