+ Reply to Thread
Results 1 to 5 of 5

How to use the if function to display multiple values correctly

  1. #1
    Registered User
    Join Date
    07-26-2013
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    8

    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.
    Last edited by rbi123; 07-28-2013 at 10:06 AM.

  2. #2
    Forum Expert
    Join Date
    07-16-2010
    Location
    Northumberland, UK
    MS-Off Ver
    Excel 2007 (home), Excel 2010 (work)
    Posts
    3,054

    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. #3
    Registered User
    Join Date
    07-27-2013
    Location
    leeds
    MS-Off Ver
    Excel 2010
    Posts
    1

    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. #4
    Forum Expert
    Join Date
    07-16-2010
    Location
    Northumberland, UK
    MS-Off Ver
    Excel 2007 (home), Excel 2010 (work)
    Posts
    3,054

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

    Try this instead:

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

  5. #5
    Registered User
    Join Date
    07-26-2013
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    8

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

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

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. index function error using match function to get data from a cell in an array
    By mabildgaard in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 10-17-2013, 02:21 AM
  2. Replies: 0
    Last Post: 01-19-2013, 01:35 PM
  3. Replies: 1
    Last Post: 03-21-2012, 11:22 AM
  4. Replies: 2
    Last Post: 03-20-2009, 01:29 PM
  5. [SOLVED] Excel - User Defined Function Error: This function takes no argume
    By BruceInCalgary in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-18-2006, 04:05 PM

Bookmarks

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