Excel/Excel formula

Advertisement


Question
QUESTION: I am trying to work out a formula that will allow me to enter a range of numbers in one cell and a different value will appear in another cell. e.g. if C19=2 then E20 will show $32.40 and if C19=3 E20=$28.80, if C19=4 D20=$25.20 etc. I believe I can only have a string of 7.  I cant one value to work but not multiples.

ANSWER: Hi Peter,

Yes, it is annoying that Excel doesn't provide a way to more easily handle situations like these.  Years ago out of my frustration I wrote a UDF (user-defined function) to help this.  Here is the code I use:

_________________

Function Cases(TestValue, ElseValue, ParamArray Selections() As Variant) As Variant

'  This is a worksheet function utility that can serve as a Select Case on a worksheet.
'  Example of usage:  =Cases(A3,"Out of Order",3,"Red",2,"Amber",1,"Green")
'  returns a string "Green" if cell A3 = 1, "Amber" if A3 = 2, "Red" if A3 = 3, and
'  "Out of Order" for all other values of cell A3.

  Dim nCases        As Integer
  Dim iCase         As Integer
  
  nCases = UBound(Selections) / 2
  For iCase = 1 To UBound(Selections) Step 2
     If TestValue = Selections(iCase - 1) Then
        Cases = Selections(iCase)
        Exit Function
     End If
  Next iCase
  Cases = ElseValue
  
End Function
______________________________

Simply paste this code into a standard macro module in your workbook.  You can then use it like this to handle your problem:

=CASES(C19,0,2,32.4,3,28.8,4,25.2)

This returns zero if C19 is none of the listed values (2-4), then follows each number with the value to return if 2, 3, or 4, etc.  You can format the formula cell as currency to get the "$".

You can add lots more tests (I don't know the actual limit, but it is large--way more than 7).  For example:

=CASES(C19,0,2,32.4,3,28.8,4,25.2,5,55.5,6,66.6,7,77.7,8,88.8,9,99.9)

And of course, any of the arguments of this function can be cell references instead of literal numbers.

I hope you find this helpful.

Damon

---------- FOLLOW-UP ----------

QUESTION: Thank you for your quick response. Unfortunately I was not able to get it to work. This probably related to my excel 2010 use and not your formula. It came up with an error. I inserted the module but it would not run.   So just clarifying, I just copy the formula into the macro. This will then provide the different dollar values (after currency format of cell) when I change the value of cell C19, E20 will display according to the formula content. As I sd I must be making an error in prep of the module.

Answer
Hi again Peter,

Sorry for not making it more clear.  The code I gave you is itself a stand-alone function.  It is not a macro, and should not be pasted into a macro, but rather pasted into a code module.  The module can have macros, and even other functions, in it, but the Function...End Function block must be a separate block of code, just as if it were a Sub...End Sub type macro.  I recommend you simply create a new macro module (in the Visual Basic Editor just use keyboard Alt-IM) and paste my code into the code pane.

As soon as it is in the code module you can use it as if it were a built-in worksheet function, calling it directly from a worksheet cell.  That is why I showed it being called preceded by an equal sign.  In your case you should enter =CASES(C19,0,2,32.4,3,28.8,4,25.2) into cell E20.

This UDF should work in Excel 95 through Excel 2013, so definitely should work in Excel 2010.

I hope this explanation clarifies things, but if not let me know and I will give you my email address to which you can send your workbook and I will incorporate it myself and send it back to you.

Damon
About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here

Excel

All Answers


Answers by Expert:


Ask Experts

Volunteer


Damon Ostrander

Expertise

I have extensive experience with VBA programming in Excel 5 through Excel 2013. As a former aerospace engineer with a large aerospace corporation and consultant in a small defense technology services company, I have developed a wide range of applications in VBA, including simulations involving mixed-language programming, satellite orbit mechanics, graphics and animation, and real-time applications. I am interested in moderate to hard VBA-related questions only.

Experience

I have developed and taught several courses in Excel VBA programming and also VBA programming in Office 97, 2000, and 2007. I have developed a number of large technical applications in Excel VBA for use within the aerospace industry.

Education/Credentials
B.S. in Electrical Engineering and Computer Science, University of California, Berkeley.

©2016 About.com. All rights reserved.