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

atoubia wrote:
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})

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.9 7,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.
--
"Actually, I AM a rocket scientist." -- JB

Your feedback is appreciated, click YES if this post helped you.


"atoubia" wrote:

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

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)

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"atoubia" wrote in message
...
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

Thank you soooo much!

"Glenn" wrote:

atoubia wrote:
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})