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)
Cases = ElseValue
Simply paste this code into a standard macro module in your workbook. You can then use it like this to handle your problem:
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:
And of course, any of the arguments of this function can be cell references instead of literal numbers.
I hope you find this helpful.
---------- 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.
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.