Can someone help me write a formula for cell D11 for the following scenario? (see attached pic for reference)
If serial number in B11 includes PGS (instead of WGS), the figure in D11 needs to be C11 * 11%. If the serial number in B11 is anything else (i.e. WGS or MGS), the figure in D11 needs to be C11 * 9.5%.
Hi Wayne,
That could be like this:
=C11*($D$9+ISNUMBER(FIND("PGS",B11))*1.5%)
I have attached a workbook with a working example so that you can see the formula at work.
The part FIND("PGS",B11) will see if the sub-string PGS exists in C11. If so, it returns a number, if not a value error.
Wrap it in ISNUMBER to turn that outcome into TRUE (=1) or FALSE (=0).
Put it all together. The formula takes the value of C11 and multiplies it by (9.5% [from D9] plus 1 x 1.5% or 0 x 1.5%), depending on if it found PGS or not.
Riny
Thanks for your reply!
I copied and pasted that formula into Cell D11, and got an Err:508.
Instead of trying to get 9.5% from D9, is there a way to write that 9.5% into the formula? (Hope I'm making myself clear)
Hi Wayne,
What software are you using? Your screenshots don't look like Excel, this is an Excel forum.
Err:508 isn't an Excel error I am familiar with. It may be that the software you are using doesn't support the functions Riny has used.
Regards
Phil
That looks like OpenOffice to me, in which case try changing the comma to a semicolon:
=C11*($D$9+ISNUMBER(FIND("PGS";B11))*1.5%)
It is OpenOffice, and changing the comma to a semicolon worked!
Thank you all so much!
