Log In
Register
Home
Active Member
January 21, 2023
Offline
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%.
See Answer
Trusted Members
Moderators
Power BI
January 31, 2022
Offline
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
Active Member
January 21, 2023
Offline
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)
October 5, 2010
Offline
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
1 Guest(s)
