Add an amount on to membership, but not if already paying enough!

[trickster1978]

Hi,

I run a subscription club to a club. We are putting in place new pricing, as there is a huge amount of historical prices in there that we need to get back in line.

In summary, the new prices will be £40 per month. Some pay this already, so will hold that rate.. so pay a lot less... as low as £22 PM.

What I need to do is increase all rates in a spreadsheet by 10%... but within this take it to a MINIMUM of £30 and not increase the max of £40.

Bit of a newbie question probably.

Thanks for any help.

Sample

£40	Keep
£35	Increase 10%
£34	Increase 10%
£22	Increase to Min £30

<tbody>
</tbody>

 

[JoeMo]

Assuming your prices are numbers not text:

Excel Workbook
	A	B
1	Old Rate	New Rate
2	40	40.00
3	35	38.50
4	34	37.40
5	22	30.00
Sheet3

 

[trickster1978]

This is brilliant- thank you.

I have just seen that there are actually a couple over £40... how can I keep the max to £40, so no increase if over?

Assuming your prices are numbers not text:
Sheet3

	A	B
1	Old Rate	New Rate
2	40	40.00
3	35	38.50
4	34	37.40
5	22	30.00

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:64px;"><col style="width:64px;"></colgroup><tbody>
</tbody>

Spreadsheet Formulas

Cell Formula B2 =IF(A2=40,40,IF(A2*1.1<=30,30,A2*1.1)) <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

 

[JoeMo]

You are welcome. To hold max to 40 try:

=IF(A2>=40,40,IF(A2*1.1<=30,30,A2*1.1))

 

[jtakw]

Hi,

I think this will give you more accurate results to cap it at 40.

Book1
	A	B
1	Old Rate	New Rate
2	40	40
3	35	38.5
4	34	37.4
5	22	30
6	37	40
7	38	40
8	39	40
9	25	30
10	36	39.6
11	42	40
Sheet16

Cell Formulas
Range	Formula
B2	=IF(A2>=40/1.1,40,MAX(30,A2*1.1))

 

[trickster1978]

You guys are awesome - thanks.

If I want to do NOTHING if over £40, what would I do?
i.e not cap and reduce.. but keep as is.. so a £40.50 stays at that, no reduction (it may be easier to give other credit than reduce the DD subscriptions)

Richard

Hi,

I think this will give you more accurate results to cap it at 40.

	A	B
1	Old Rate	New Rate
2	40	40
3	35	38.5
4	34	37.4
5	22	30
6	37	40
7	38	40
8	39	40
9	25	30
10	36	39.6
11	42	40

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet16​

Worksheet Formulas Cell Formula B2 =IF(A2>=40/1.1,40,MAX(30,A2*1.1)) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

 

[jtakw]

You guys are awesome - thanks.

If I want to do NOTHING if over £40, what would I do?
i.e not cap and reduce.. but keep as is.. so a £40.50 stays at that, no reduction (it may be easier to give other credit than reduce the DD subscriptions)

Richard

Then we add another IF test in front:

Book1
	A	B
1	Old Rate	New Rate
2	40	40
3	35	38.5
4	34	37.4
5	22	30
6	37	40
7	38	40
8	39	40
9	25	30
10	36	39.6
11	42	42
12	40.5	40.5
Sheet16

Cell Formulas
Range	Formula
B2	=IF(A2>=40,A2,IF(A2>=40/1.1,40,MAX(30,A2*1.1)))