• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

I think an array would help??? [SOLVED]

red_leader

red_leader

New Member
Hi - I have a set of values in column A that I need to multiply by a modifier selected from a list of values in column B. The final value would be displayed in column C. The user has a dropdown list of avail modifiers in col B that range from +25% to -25%. I cannot figure out how to write the formula so that XL recognizes the correct modifier selected by the user in Col B and multiplies it against the Col A value. To further complicate things, I have used wingdings (symbols) to represent the modifiers in Col B (i.e., two down arrows for a modifier of -25%, 1 down arrow for a modifier of -10% etc.) I think a long IF formula might work but I can't figure out the syntax and it seems overly complex. I haven't used arrays much but I wonder if this situation calls for one?? Is there anyway to do this and to preserve the use of the symbols/wingdings at the same time? If I can pull this off my VP will be mightily impressed and maybe I can finally talk him into paying for me to take a Chandoo class!! :) Your help is greatly appreciated!!
 
Hi ,


I think your post makes the problem more complicated than it actually is !


Your first two sentences say that all you need to do is multiply a value in column A by a value in column B to give a result in column C.


The only complication is that your modifiers are based on a font ; going through the list of WingDings , I can see that WingDings 3 gives the following :

[pre]
Code: 
66      -25%
6       -10%
22        0%
5        10%
55       25%
[/pre]
where a value of 66 will display 2 downward pointing arrows , while 55 will display 2 upward pointing arrows.


We can name this range of 5 rows and 2 columns as Modifiers_Table , and use it to get the result in column C by :


=A2 * VLOOKUP(B2,Modifers_Table,2,False)


The data and display in column B will in no way be affected , because the display uses the cell formats , while the formulae in column C use the underlying values , which get translated by way of the lookup table.


Of course , I may be way off the mark ; if so , please give a few examples of your data , calculations and expected results.


Narayan
 
Hi Narayan - thanks for your response! However, I must be doing something wrong. I set up the modifiers table on a separate worksheet and named the range Modifiers_table. Then I plugged the formula in for the first cell, but I'm getting a #name? error. The formula I'm using is based on what you suggested: =$K$5*VLOOKUP($L$5,Modifers_Table,2,FALSE), where K5 is the cell holding the value I want to be modified by the number selected by the user from in cell L5. Also, the wingdings I'm using are pp,p,|},q,qq. Not sure what is wrong... I can send you a copy of the sheet if that helps, but here's a text version of what it looks like now.


Line Risk Summary Modifier Total

66.7% qq #NAME?
 
You must log in or register to reply here.
Back
Top