Hi all,

I've got a 50,000+ products catalogue coming from more than 50 suppliers (manufacturers and wholesalers).

I need to calculate my purchase price based on a text input, which is given by the Vendor to express the discounts (in%) from the list price. This discount varies :

- from vendors to vendors,

- from collection to collection

- from reference to reference.

It also can change according to the quantity purchased.

Our best discounts might be expressed as :

50+10+10

and 50+10+10+5 if we happen to purchase a whole pallet for this vendor.

When building the solution, I never thought this way of presenting discount would have lapsed so long, but it seems vendors will keep taking us for some fools with these "expressions", considering the longer the "calc" is, the better the sales might be.

As a matter of fact 50+10+10 could also be expressed as 55+5, but I have rebates going up to 30+20+10+10+10+5 !!

So given a price of 25$, 50+10+10 would be 25*.5*.9*.9 = 10.125$

How ?

Thanks.

Ugo...

Check me if I'm wrong, but on $100 50+10+10 is

100 * .50 = 50 * .90 = 45 * .90 = 40.5

But 55+5 on the same $100 is

100 * .45 = 45 *.95 = 42.75

I need to know if this is right before we begin on any calculation.

Absolutely..

See, they even got me confused.

In fact 55+10 = 50+10+10....

To be complete, but this wouldn't be a problem once the track is opened, some discounts are shown as 50 10 10 (without the +)...

Ugo...

So then I would begin with something like

Abs(TexttoNum(LeftWords(DiscountTest,1)-100))*.01

or

100 - TexttoNum(LeftWords(DiscountTest,1)) * .01

And the take the next word, etc.

Neat one !

I was going

Number * ((100-MiddleWords(discount,1,1))*.01) * ((100-MiddleWords(discount,2,1))*.01) *((100-MiddleWords(discount,3,1))*.01) *((100-MiddleWords(discount,4,1))*.01) *((100-MiddleWords(discount,5,1))*.01) *((100-MiddleWords(discount,6,1))*.01)

Yours is surely cleaner...

Thanks

same thing... different look

That's what makes this so interesting

Ugo....

when Ray sees this post he will do it in one line.... ten words or less.

Hmm... doesn't work as expected.

Middlewords, which I came up just writing this post (as usual !) seems a workable solution, but I'm not sure it won't break either.

Riley Waugh said:

Ugo....

when Ray sees this post he will do it in one line.... ten words or less.

I'm not in a hurry, I'll wait then...