Help writing a formula

[TheBigCountry]

Thanks in advance for all the help you've given thus far.

I wanted to write a formula that has a couple of parameters.

Each customer can have a value of either 30 or 120. Each value has certain benefits (I won't bore you with the background as to why)

1. If the customer has a value of 30 - they can receive up to 140 points (for a loyalty program) . So if the cumulative total of their orders in the month is 90, they get 90 points. If it's 139, it's 139 points, if it's 141+, they still only get credit of 140.

Would an appropriate fomula be =IF(C3<141;C3;140)
(C3 is the sum of all orders in the month)

2. If the customer has a value of 120 - they can receive up to 140 loyalty points. They can receive 60 bonus loyalty points (200 total), but only if their order value exceeds 240.

Example: order total is 139 - points = 139.
Order total is 141 - points = 140
Order total is 240+ - points = 200

Basically
- Order Total ≤ 140 = Order Total

- 140 ≤ Order Total < 240 = 140 Points

- Order Total ≥ 240 = 200 Points

How would I write a formula for that?

 

[Shteven]

I will use CV to refer to Customer Value in my answer. Replace it with the appropriate cell reference:

=IF(CV=30,IF(C3>140,140,C3),IF(C3>140,IF(C3>=240,200,140),C3))

 

[David Evans]

What version of Excel are you using? Have you got a sample file to post here - it saves people having to create one from scratch and possibly heading off on a tangent ...

 

[Chihiro]

Here's another version.

=IF(CV=30,IF(C3>140,140,C3),CHOOSE(SUM(--(C3<{9E+25;240;140})),240,140,C3)

 

[TheBigCountry]

Hi David, Here is the file. CV value is in D2 and F2. Order total is in C column. I'm using Office Professional Plus 2013.

Shteven and Chihiro, when I try to paste in your formulas, it gives me errors?

 

[TheBigCountry]

Here's another version.

=IF(CV=30,IF(C3>140,140,C3),CHOOSE(SUM(--(C3<{9E+25;240;140})),240,140,C3)

I was able to finally get your formula to work by adding an additional parenthesis.

However, when I tried your formula on a CV value of 30, and their order total was 30, it returned a value of 140.

For any order that is equal or less than 140, it should return the value of the order. In this case, the order total of 30 should be the returned value.

 

[TheBigCountry]

I will use CV to refer to Customer Value in my answer. Replace it with the appropriate cell reference:

=IF(CV=30,IF(C3>140,140,C3),IF(C3>140,IF(C3>=240,200,140),C3))

This formulas seems to almost work. It works for all order equal or less than 140.

However, when a Customer has CV=30, they shouldn't be able to get points above 140. So an order total of 300 should return 140. This formula is returning values above 140, though in the correct manner for a CV=120.

 

[Shteven]

Editing this reply as this message was previously incorrect.

In cell D4:
=IF(C4>140,140,C4)

In cell F4:
=IF(C4>140,IF(C4>=240,200,140),C4)

 

[Chihiro]

You have CV in separate columns? Then you don't need to combine the two formula. See Col H & J in attached.

However, normal set up would be to have Customer name & Customer value (CV) for the customer in same row. That's why we combined the two formula.

See Col F in attached.

 

[TheBigCountry]

You have CV in separate columns? Then you don't need to combine the two formula. See Col H & J in attached.

However, normal set up would be to have Customer name & Customer value (CV) for the customer in same row. That's why we combined the two formula.

See Col F in attached.

Thank you, I've kind of a novice on how to structure these formulas and spreadsheets so if having them combined in the same row would be helpful, I'm game for that. Thanks for all of your help!

 

[TheBigCountry]

Editing this reply as this message was previously incorrect.

In cell D4:
=IF(C4>140,140,C4)

In cell F4:
=IF(C4>140,IF(C4>=240,200,140),C4)

Thanks!!