• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

nested if equation concatenate to combine text $ but 3rd condition not work

K

karynd

New Member
Good Evening


I have 3 conditions and the formula I am using gives the correct answer for "no reinstatement" and "1 x reinstatement" but when calcuations!C37 has "2 x reinstatement" it supplied the same answer as "1 x reinstatement".


calculations!C28 is a drop down box with the 3 conditions and depending on the condition it is a value multiplied as set out in the below formula. The answers combined 2 dollar values and text and I have the formula correct to give the correct wording and formatting etc but the 3rd condition will not show correctly.

I have been playing around with the formula and was really happy when I got the first 2 conditions to work but when I added the 3rd condition I am a loss


Note I dont really need to have the false condition as "error" the 3rd condition could be the false condition since C28 is a drop down box with only the 3 conditions but I tried it that way too and it did not work - again I only could ever get the 2 conditions to work even if I had error as the false if you went to C28 and changed it to 2 x reinstatement it did not give an error it still gave the answer to 1 x reinstatement.

Maybe my formula is too long?


Sorry for the large essay to explain


thanks and I really appreciate any assistance


cheers


Karyn


=IF(calculations!C37="no reinstatement",CONCATENATE(TEXT(calculations!C28,"$#,##0")," ","any one claim and ",TEXT(calculations!C28,"$#,##0")," ","in the aggregate for all claims made during the period of insurance"),(IF(calculations!C37="1 x Reinstatement",CONCATENATE(TEXT(calculations!C28,"$#,##0")," ","any one claim and ",TEXT(calculations!C28*2,"$#,##0")," ","in the aggregate for all claims made during the period of insurance"),(IF(calculations!C37="2 x Reinstatement",CONCATENATE(TEXT(calculations!C28,"$#,##0")," ","any one claim and ",TEXT(calculations!C28*3,"$#,##0")," ","in the aggregate for all claims made during the period of insurance"),"error")))))
 
Hi Karyn,


I have put your formula in my excel sheet and checked the conditions as per below:


In Cell(C28) i have added list which has values 10, 20 ,30 and in Cell(C37) added a list which has values "no reinstatement", "1 x Reinstatement" and "2 x Reinstatement".

Keeping the C28 value to 10 and changing the C37 value for each, I got below result:


$10 any one claim and $10 in the aggregate for all claims made during the period of insurance

$10 any one claim and $20 in the aggregate for all claims made during the period of insurance

$10 any one claim and $30 in the aggregate for all claims made during the period of insurance


It means all 3 conditions are working fine. Please cross check the values in your list in Cell C28.


Let me know if I have understood your query wrong.


Thanks & Regards,

Anupam Tiwari
 
Hi Karynd,


Can you please try the below forumla:


=IF(C37="no reinstatement",C28&" any one claim and"&C28&" in the aggregate for all claims made during the period of insurance",IF(C37="1 x Reinstatement",C28&" any one claim and"&C28&" x 1 in the aggregate for all claims made during the period of insurance",C28&" any one claim and"&C28&" x 2 in the aggregate for all claims made during the period of insurance"))


Thanks,

Suresh Kumar S
 
Hi Karyand,


I Tried my level best to decrease the length of the Formula, but alas.. Not success below this.. :)


Code: 
="$ " & Calculations!C28 & " any one claim and $" & MATCH(Calculations!C37,{"no reinstatement","1 x Reinstatement","2 x Reinstatement"},0) * Calculations!C28 & " in the aggregate for all claims made during the period of insurance"


Regards,

Deb
 
Thanks everyone what a great forum


anupam thank you for testing my formula and you understood perfectly -you are right it has something to do with cell c37 where I had to add the 2xreistatement in the drop down box for some reason it does not seem to be reading it

I now just have to work out what I messed up in adding the extra to the drop down box -at least I know it is not my formula


Cheers

Karyn
 
You must log in or register to reply here.
Back
Top