If statement

[Alma]

I got help for this formula from the forum a while back. Now I need to make some changes. Could you please help . . . again

( please see the attachment)

The spreadsheet is used as an Audit tool to review the accts worked:

Each P error code entered should deduct 20% of a 100% (multiple codes can be entered).


If no errror is entered in the error code field - it should show 100% in the calculation column


Each F error code should show 0%
Thank you!

[mrice]

Use this formula pasted into C24

Please Login or Register  to view this content.

[Alma]

Thank you so much! It works great!

[Alma]

Just one thing . . " F" error codes are not showing 0% .

Everything else work perfect.

( blank error code = 100%
P code 20% reduction - multiple P codes can be posted in one cell
F codes = 0%)

Thanks

[corinereyes]

Hi Alma,

Maybe?

=IF(LEFT(B24,1)="F","", 0)

[Alma]

yes, that one would work for the "F" code by itself. What I need is one formula that will show :

100% if the cell is blank
0% if the cell shows any "F" code ( could be F10, F2, F15 . . .) and
it should show 20% reduction ( of a 100%) for any P code entered ( multiple codes could be entered in the same cell devided by a "," sign.

The formula MRICE sent me works perfectly for blank cells and P codes. I need to add something to it to show 0% when a "F" error code is entered.

Thank you both for your help!

[daddylonglegs]

Perhaps try

=IF(LEFT(B24)="F",0,1-(LEN(B24)-LEN(SUBSTITUTE(B24,"P","")))/5)

[Alma]

it is not working. " F" codes are still showing 100% instead of 0%.

Thanks for your help

[Alma]

I have no idea what I did the first time I tried to formula so it did not work-
it works great now!

Thank you all for your help!