I entered following in cell G3 =IFERROR(IF(D3="Absent","",MROUND((C3*D3)/30,1)),C3)

how to add substitute function in above formula as in cell D3 may contain word like 15 days, 10 days, 25 days, etc. in place of the word "Absent"?.

Explanation:

If D3=Absent then in G3=0

if D3=15 days, etc. then in G3= value of number in D3 multiplied with C3

Else G3=C3

I'm a bit confused. 2 questions:

1 - Your formula includes this:

MROUND((C3*D3)/30,1))Yet your explanation says this:

if D3=15 days, etc. then in G3= value of number in D3 multiplied with C3What happened to the MROUND portion of the formula?

2 - re: Else G3=C3

What "else" might be entered in D3? Is there really an "else" to deal with?

All that said, does one of these work for you?

If no "else" will exist:

=IF(D3="Absent",0,MROUND((C3*LEFT(D3,FIND(" ",D3)-1))/30,1))

If an "else" will exist:

=IFERROR(IF(D3="Absent",0,MROUND((C3*LEFT(D3,FIND(" ",D3)-1))/30,1)),C3)

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code

Ask Your Question

Weekly Poll