If Formula Question

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.
 
M

MartinW

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
 
A

Ady

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
 
R

Rick Rothstein \(MVP - VB\)

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

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

Rick
 
R

Rick Rothstein \(MVP - VB\)

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
 
Top