Forum Discussion

Andrew58's avatar
Andrew58
Copper Contributor
Dec 06, 2021
Solved

Help with an Excel Formula/Command Please

Good Afternoon,

I need help with a formula which I am trying to put into an Excel Spreadsheet please.

I'm not sure how to do what I want to do.    Please See The Diagram Below.

I am wanting to put a formula into cell E3 so that the 'correct' currency name & symbol will display in this cell (E3) when a particular currency is selected from the yellow box.

The formula would need to be something like "(if I4 = 1 then E3 equals H3) or (if I4 = 2 then E3 equals H4) or (if I4 = 3 then E3 equals H5) or (if I4 = 4 then E3 equals H6). That way the (spreadsheet) user can select their preferred currency, by simply putting the appropriate number in cell I4 (light yellow colored) and then the corresponding symbol/currency type will post over to E3 in the 'Currency Convertor" (from column H).

I hope someone can help.

Many thanks, Andrew.

 

  • Don Kirkham's avatar
    Don Kirkham
    Dec 07, 2021
    Glad to help. Based on your screenshots, I thought cells I3:I6 were merged, which means the value is stored in I3. It looks like you merged I4:I6, which is why your correction worked! I really glad that this caused you to do some research and learn about some awesome formula capabilities in Excel.

    Please mark one of my responses as the Best Response. Thanks!
  • Don Kirkham's avatar
    Don Kirkham
    MVP
    Dec 06, 2021

    You can use nested IF statements to achieve the desired result. This should work:

    =IF(I3=G3,H3,IF(I3=G4,H4,IF(I3=G5,H5,IF(I3=G6,H6))))

    If this answers your request, please consider marking it as the approved solution. Thank you!

    • Andrew58's avatar
      Andrew58
      Copper Contributor
      Dec 07, 2021
      Hi Don - UPDATE - You were exactly right - it was just that you had 1 simple error in your formula (very easy to do).
      The I3 (which was the reference cell) should have been I4
      I used your formula & substituted I4 for your I3 & it worked perfectly. Brilliant, thank you.
      I must admit that by having this small error in your response to me, it made me think a lot about 'Nested IF statements', so I learn't a lot (Deliberate on your part ? 🙂 )
      Thank you again for your help Don,
      Kind Regards,
      Andrew (Gold Coast, Australia)
      • Don Kirkham's avatar
        Don Kirkham
        MVP
        Dec 07, 2021
        Glad to help. Based on your screenshots, I thought cells I3:I6 were merged, which means the value is stored in I3. It looks like you merged I4:I6, which is why your correction worked! I really glad that this caused you to do some research and learn about some awesome formula capabilities in Excel.

        Please mark one of my responses as the Best Response. Thanks!
    • Andrew58's avatar
      Andrew58
      Copper Contributor
      Dec 06, 2021

      Don Kirkham 

      Hi Don, 

      Thank you so much for taking time to try & help me with the required formula.

      I think we are getting close with your suggestion & I'm pleased that there obviously is a solution out there to be found, however the formula you gave me just didn't quite work. I had a play around but couldn't get it right.

      Here's (a screenshot of) what came up on the sheet for me,

      Could it be something to do with,

      * the possible requirement for a 'value if false' ?

      * Gaps in the formula after the commas ?

      * Use of the 'square brackets' [    and    ] ? 

      I hope this helps you to possibly 'tweek it up' a little, so it works 🙂

      Many Thanks,

      Andrew

Resources