Excel Formula

Occasional Contributor

I've been struggling to answer a question I was given in a task to complete and I think my problem is how I'm looking at solving it, can someone please shed a light on how to solve this. Added below are the question and my solution(which doesn't completely answer the question). I'd very much appreciate any help.excel .pngexcel 2.png

4 Replies

@Terry-Martins 

 

Well, first, this looks very much like a homework assignment, and those of us who frequent this forum answering questions have agreed that it's not our task to do folks homework for them.

 

That said, I think you're saying that your solution is the IF formula that appears in the image. I've not looked further into what it's doing and why you think it's inadequate. It's hard to do when all one has is an image.

 

My suggestion would be that you investigate the use of a table showing the discounts for various conditions and the use of the VLOOKUP function as a means to reference that table and determine the relevant discount. The link in that sentence will take you to a helpful internet resource for researching how to use Excel. If that function itself doesn't work for you, there will be other suggestions on the page. I suggest you poke around there diligently.

Thanks for this, I had no idea about the homework assignment agreement between the regular users of this platform but I appreciate you bringing it to my notice. I found the right way to answer the question with Nested IFs(=IF([@Events]>=10,[@Price]*0.8,IF(AND([@Events]>4,[@Events]<10),[@Price]-50,[@Price])))

@Terry-Martins 

 

Thanks for this, I had no idea about the homework assignment agreement between the regular users of this platform but I appreciate you bringing it to my notice.

 

I'm glad you appreciate it. For what it's worth, by "regular users" I have in mind a number of us who are among the more active in responding to questions. It's not as though there's a written agreement or "terms of use" that everybody supposedly agrees to. 

 

But I hope it also makes sense to you AS a student. You're going to learn Excel more through struggling through to an answer than you will if it's presented to you on the proverbial silver platter.

 

FWIW, your solution works well when the various conditions are limited in number. Now that you've resolved it with the use of some nested IF functions--which can become unmanageable when too many layers deep--you might want to research the IFS function, which helps to a great degree but still has logistical limits. Beyond that, using tables with some of the various LOOKUP functions (as well as INDEX and MATCH) can be very powerful for handling multiple conditions.

I do agree with your perspective on the path to learning Excel better and I'll learn the other functions as well, I appreciate the time and effort.