# How to use the if function to display multiple values correctly

1. ## How to use the if function to display multiple values correctly

Hi everyone,

I am trying to write a formula using the if function,

I have a combo box with the following list inside,

No Of Quarantined Incidents
No Of Units Quarantined
No Of Bins Quarantined
Total Cost Quarantined
Average Units Per Incident
Average Bins Per Incident
Average Cost Per Incident

I have 1 cell that will display the above results, I want that cell to display the values correctly in the correct format, so if I choose Total Cost Quarantined or Average Cost Per Incident there will be a £ sign and the value, if I click on the others I want them to display the values without the £ sign. I can get the £ sign with the values with the following =IF(K100="Average Cost Per Incident",CONCATENATE("£",INDEX(tblChosen,\$J\$107,\$P\$101)),INDEX(tblChosen,\$J\$106,\$L\$101)). But where do I put the "Total Cost Quarantine" inside the formula??, I have tried numerous ways but I'm having no luck. P.S K100 is a cell that displays whatever has been chosen in combo box. Thank you very much for you help.

2. ## Re: if function

I don't think you need to nest IF statements at all:

=IF(ISNUMBER(FIND("Cost",K100)),TEXT(INDEX(tblChosen,\$J\$107,\$P\$101),"£0.00"),INDEX(tblChosen,\$J\$107,\$P\$101))

3. ## Re: if function

Hi Andrew,

I've been having trouble logging in with my previous user name (rbi123), I have used your formula and it works perfectly - and thanks for your effort on that, however, when I click on the combo box and choose No Of Units Quarantined it displays the values correctly but without the commas for thousands or millions with your formula, is there a way to do this?? if there is I would like it to display the commas for
No Of Quarantined Incidents
No Of Units Quarantined
No Of Bins Quarantined
Average Units Per Incident
Average Bins Per Incident

I really appreciate this Andrew and many thanks for your time and effort

4. ## Re: How to use the if function to display multiple values correctly

=IF(ISNUMBER(FIND("Cost",K100)),TEXT(INDEX(tblChosen,\$J\$107,\$P\$101),"£0.00"),TEXT(INDEX(tblChosen,\$J\$107,\$P\$101),"#,##0"))

5. ## Re: How to use the if function to display multiple values correctly

Works brilliantly, thank you very much for your time - its appreciated.

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1