scaling reduction of a price based on GP Value in another column

**Steve1977** · 07-21-2023, 08:52 AM

I've created a file that works out the Gross Profit of an item based on the cost and selling price. The file also acknowledges if that GP is above or below 25% and it then it's instruction to put a note in Column H. In that it puts a note if it's above 25% GP and it leaves it blank if its less than 25%

Then in Column I, it knocks 10% off the figure in Column B if it's above 25% GP and if it's less than 25% then it simply uses the price from Column B

However I'm looking to create something my dynamic now in the sense that if a price is between 30-35% GP, then it knocks 15% off...between 35-40% it knocks 20% off.

I've attached the Excel file and if anyone is able to help whether that be via a Formula or VB then it would be very much appreciated.

scaling reduction based on GP Value2.xlsx

**AliGW** · 07-21-2023, 09:01 AM

Is this what you mean?

=IF(H3="",B3,B3*LOOKUP(E3,{0.25,0.3,0.35},{0.9,0.85,0.8}))

**AliGW** · 07-21-2023, 09:47 AM

If that takes care of your original question, please choose Thread Tools from the menu link above and mark this thread as SOLVED.

Also, if you have not already done so, you may not be aware that you can thank anyone who offered you help towards a solution for your issue by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of all those who offered help.

**ExceLogan** · 07-21-2023, 11:14 AM

hi there, I allow to propose an alternative formula to avoid errors for values less than 25%:

Please Login or Register  to view this content.

I hope results useful.

**AliGW** · 07-21-2023, 11:15 AM

There are no errors in my formula for values under 25%! If it's under 25%, H3 is blank, so my formula returns blank.

**HansDouwe** · 07-21-2023, 12:44 PM

The OP tells:

if it's less than 25% then it simply uses the price from Column B

**Steve1977** · 07-24-2023, 11:14 AM

Originally Posted by HansDouwe

The OP tells:

Originally Posted by AliGW

Is this what you mean?

=IF(H3="",B3,B3*LOOKUP(E3,{0.25,0.3,0.35},{0.9,0.85,0.8}))

These are both great guys, the only thing I would amend on the first one from AliGW, is that if it's below the cost price i.e. negative, it wouldn't take off any discount. How could I amend this to incorporate that?

In the second one from HansDouwe, it works a treat but I'm unsure how exactly it works. What instructions are taking off the 10, 15 and 20% ?

Idea being, if I understand how it's doing this I would ideally like to implement an instruction whereby, if it's over 40% then it takes off 25%

**HansDouwe** · 07-24-2023, 11:37 AM

In the second one from HansDouwe

I have not provided a solution. Do you mean ExcelLogan's solution?

Originally Posted by ExceLogan

=B3*LOOKUP(E3,5*{0,5,6,7}%,1-5*{0,2,3,4}%)

if it's over 40% then it takes off 25% please try (build on the formula of ExceLogan):

Formula:

Please Login or Register  to view this content.

I just removed the 5* from the formula and multiplied the percentages in the table all by 5 to make it easier to understand.
The formula searches for the corresponding discount percentage via the value in E3,

**Steve1977** · 07-25-2023, 03:28 AM

ahh thank you Hans and indeed thank you to everyone who posted. Really appreciate your help!

**AliGW** · 07-25-2023, 03:37 AM

Glad to have helped.

If that takes care of your original question, please choose Thread Tools from the menu link above and mark this thread as SOLVED.

Thanks for the rep.

**HansDouwe** · 07-25-2023, 04:39 AM

Thanks for the feedback and rep

. Glad to have helped.

**Steve1977** · 07-25-2023, 09:53 AM

Originally Posted by HansDouwe

Thanks for the feedback and rep

. Glad to have helped.

One slight tweak i have in mind is that it doesen't seem to take off percentages of .5. i.e. 12.5% instead of 12. I've tried amending the forum to "12.5" or "125" but it doesn't seem to be work

**Steve1977** · 07-25-2023, 09:56 AM

ignore me, i didn't do it right.

scaling reduction of a price based on GP Value in another column

LinkBack

Thread Tools

Rate This Thread

Display

scaling reduction of a price based on GP Value in another column

Re: scaling reduction of a price based on GP Value in another column

Re: scaling reduction of a price based on GP Value in another column

Re: scaling reduction of a price based on GP Value in another column

Re: scaling reduction of a price based on GP Value in another column

Re: scaling reduction of a price based on GP Value in another column

Re: scaling reduction of a price based on GP Value in another column

Re: scaling reduction of a price based on GP Value in another column

Re: scaling reduction of a price based on GP Value in another column

Re: scaling reduction of a price based on GP Value in another column

Re: scaling reduction of a price based on GP Value in another column

Re: scaling reduction of a price based on GP Value in another column

Re: scaling reduction of a price based on GP Value in another column

Thread Information

Users Browsing this Thread

Similar Threads

Determine Rating A-F based on percentage reduction

Change dates based on time reduction

I want to autopopulate column B with a price based on 70% margin.

annual part price reduction formula

[SOLVED] How do you use a scaling price markup within a single cell?

Returning unit price based on Qty from a customer price book with price qty breaks

Calculate new figure based upon % reduction table

Bookmarks

Bookmarks

Posting Permissions