If Formula Question

  • Thread starter Thread starter Ady
  • Start date Start date
A

Ady

Hi There

I am struggling with what appears to be an easy formula. Here is what I am
trying to do:

If the values of cell vale of B4 begins with either 20 or 30 then the
(B20*0.5)

Basically if a part number is for example 20-504 or 30-555 then we need to
give it 50% discount. We have many part numbers beginning with 20 or 30 that
need 50% discount.

Any help or advice offered will be most welcome. Many thanks in advance.
 
Hi Ady,

Probably needs a bit of adjusting to suit your actual
data, but try something like this,
=IF(OR(LEFT(B4,2)*1=20,LEFT(B4,2)*1=30),B20*0.5,B20)

HTH
Martin
 
Hi There

Many thanks indeed to both of you. Not quite sure how this works, but it
does work very well!

Thank you again.

Kind regards

Ady
 
You can make that formula a little bit shorter...

=IF(OR(--LEFT(B4,2)={20,30}),B20*0.5,B20)

Rick
 
Actually, even a couple of characters shorter...

=IF(OR(LEFT(B4,2)={"20","30"}),B20/2,B20)

or, by changing the approach slightly, shorter still...

(of course, we are now changing the "approach")...

=B20-OR(LEFT(B4,2)={"20","30"})*B20/2

Rick


Rick
 
Back
Top