Excel Spreadsheet to Calculate Coupon Savings – Alternative to “If Or Else”

Excel spreadsheets (.xls)

We’re thrilled to say that we’ve been getting an increasing amount of reader questions with their own excel challenges or problems. It’s always interesting because no matter what the subject or the exact problem is, we can usually learn something that can be applied at some point in the future. In this case, we received an email from a reader that is attempting to save how much savings she is making using simple coupons. The only twist? All coupons of $0.50 or less are doubled by the store. Simple enough? Our reader was attempting to either use the “nested IF statements or an “else if”. She also sent me a sample file which I’ve modified slightly and will let you see at the end of this post.

As in almost any other excel problem, the main challenge is usually to “see the problem in the simplest way”. It’s much easier said than done of course. In this case, what is the situation exactly?

-Buyer buys a given number of items
-For each item there may be a coupon
-That coupon should be applied but any coupon worth less than $0.50 should be doubled in value.

Personally, I think we can easily resolve this problem with a simple “if” condition without adding any “or else” or “nested if” conditions. It’s always about finding the easiest way which not only saves us time but also makes it much easier to modify and improve the fills.

In this case, here is how I see it:

Personally, I would simply calculate the total as follows:

Total = If coupon > $0.50, Quantity x Price – Coupon Value, Quantity x price – 2 * Coupon Value

Why? There are really only 2 cases here. A coupon worth less than $0.50 or not.

cell E2 = IF(B2>0.5,C2*D2-B2,C2*D2-2*B2)

That’s it!! You can download our spreadsheet here!

If ever you have questions relating to this or any other excel issue, please either ask on this post or contact us!

***************************************************

Look Good at Work and Become Indispensable Become an Excel Pro and Impress Your Boss


***************************************************

6 Feedbacks on "Excel Spreadsheet to Calculate Coupon Savings – Alternative to “If Or Else”"

harrison gowe katana

I have find help from your explanation of if function in excel. now am very much ok. if i have other misunderstanding iwill let you know.
thanks in advance



viney

if function not working above eight condition



Anand Shah

i want formula for identify defined range for unique value



vipul

70000 -1000$

1000000 – 1200000 – 2000$

1200000 – 1500000 – 5000&

How To Use “IF” Function



Jay

Excel And OR function in conditional formatting:

=OR((AND($C:$C=”V”,$D:$D<=20)),(AND($C:$C="S",$D:$D<=10)))

This formula not working to calculate reorder level



Leta

Excellent web site you have got here.. It’s hard to find good quality writing like yours
nowadays. I really appreciate people like you!
Take care!!