If Formula from Drop down

[hardeep.kanwar]

Hi!!

I am using IF formula using Drop Down.

Drop Down List

1: SGST/CGST
2: IGST

Other Condition in J3,K3,L3 i.e. SGST@9% CGST@9% IGST@18% RespectivelyNow i am using below Formula for IGST@18% its working and calculate the Value fine

Formula is for IGST@18%= =IF($R5&"@18%"=$L$3,$H5*18%,"0")

But i have tried and its not working in other conditions i.e SGST@18% and CGST@18%

Below is the Formula for both Conditions :=IF($R5&"@9%"=$J$3&"/"&$K$3,$H5*9%,"0")

Any idea where i am lacking

Thanks in advance

 

[NARAYANK991]

Hi ,

I can only imagine , since your post does not explicitly mention it , that the dropdown is in R5.

Then R5 can either contain the text string SGST/CGST or the text string IGST.

The first formula is : =IF($R5&"@18%"=$L$3,$H5*18%,"0") ; if R5 contains the text string IGST , then the formula would reduce to :

=IF("IGST" & "@18%" = $L$3 , $H5*18% , "0")

which would further reduce to :

=IF("IGST@18%" = $L$3 , $H5*18% , "0")

and since L3 does contain the text string IGST@18% , this formula would correctly calculate $H5 * 18%.

However , if the dropdown has selected SGST/CGST , then things are different , because now the formula is :

=IF($R5&"@9%"=$J$3&"/"&$K$3,$H5*9%,"0") , which would reduce to :

=IF("SGST/CGST" & "@9%" = "SGST@9%" & "/" & "CGST@9%" , $H5*9% , "0")

which would further reduce to :

=IF("SGST/CGST@9%" = "SGST@9%/CGST@9%" , $H5*9% , "0")

Obviously the condition is not satisfied since the two text strings are not identical.

Your formula for SGST/CGST needs to be changed to :

=IF(SUBSTITUTE($R5 , "/" , "@9%/") & "@9%" = $J$3 & "/" & $K$3 , $H5*9% , "0")

Narayan

 

[hardeep.kanwar]

Hi Thanks for the Reply

I am Attaching the Sheet for better Clarification

Conditions are :
1: If R5 is Equal to J3 and K3 than Calculate the 9% of H5, L4 Remains "0"


2: If R5 Is Equal to L3 than Calculate the 18% of HG, J4 and K4 remains "0"

 

[bosco_yip]

Try.....

In J4 formula copy across to L4 and all copy down :

=IF(ISNUMBER(FIND(LEFT(J$3,4),$R4)),$H4*MID(J$3,6,3),0)

Regards

 

[hardeep.kanwar]

Thanks a Ton Dear

This is what i am looking