List price and Discount Manipulator to Get the Target Net price

Hi All,

Im new to macro excel and i tried to write a code but i cant make it work. Please help me.

I am trying to do a macro that calculates the nearest discount to match the Net Price or to match the Target Net Price

Example
Given :
LP= List price = 2345.0900
NP=Net price = 1999.8000
Discount = ROUND ((LP-NP )(LP/100),4)
Discount = 14.7240

**Target Net Price = 1999.8000

Reverse calculation;
Given:
LP = List Price = 2345.0900
D = Discount = 14.7240
NET PRICE = round (LP-(LP*(D/100)),4)
NET PRICE = 1999.7989
Which does not satisfy the condition.

CONCLUSION: Reverse calculation is less than the target net price. Need to subtract 0.0001 from discount to satisfy the condition

Please generate a macro so that when it does a reverse calculation, the net price is always equal or greater than the target net price. To satisfy the condition, the discount should become D-0.0001

NEW NET PRICE FORMULA

NET PRICE = round(LP-(LP*(D-0.0001)/100))),4)
NET PRICE = 1999.8013
Which satisfies the condition.


P.s. If net price is still less than the target price
The subtractor will be 0.0002 and so on. Until calculated Net price is greater than or equal to target net price.

in another column Please help me create a macro to calculate the Target Net price by varying the List price and the Discount to get the 100% exact Match Net price.
By Varying the List price it should be below than the List price
By Varying the Discount got no restriction but most likely it will got lesser.

From the example above; Original List price = 2345.0900 ; Original Discount = 14.7240 ; Target Net Price = 1999.8000

Manipulated List price - 2345.0885 - The Manipulated List price is Less than the Original List price - Correct
Manupulated Discount - 14.7239
Net Price - 1999.80

Please help me.

Many thanks.

If I understand correctly, you don't need VBA to solve this problem.
If you calculate the discount using ROUNDDOWN instead of 5/4 rounding and then calculate the list price from the target price using the new discount to mark up you should get the result you want. e.g

Discount in column F =ROUNDDOWN(IF(A2="","",(A2-B2)/(A2/100)),4)
List price in Column E =D2/((100-F2)/100)

This will give a manipulated list price always less than or equal to original list, and a net price equal to target price

Hi, thank you. I will refunitely try that.

Originally Posted by thompson_djc

If I understand correctly, you don't need VBA to solve this problem.
If you calculate the discount using ROUNDDOWN instead of 5/4 rounding and then calculate the list price from the target price using the new discount to mark up you should get the result you want. e.g

Discount in column F =ROUNDDOWN(IF(A2="","",(A2-B2)/(A2/100)),4)
List price in Column E =D2/((100-F2)/100)

This will give a manipulated list price always less than or equal to original list, and a net price equal to target price