Hi Guys,
I've got a excel formula question. Say I've got a bunch of excel data like this:
A1 A2 A3 B1 B2 B3 C1 C2 C3
I've also got something like this
1 x 2 y 3 z
This is what I want to do.
- If A1 is less than or equal to 1, then input x. If A1 is less than or equal to 2, then input y. If A1 is less than or equal to 3, then input z.
- Same for all the data within A1 - C3
What sort of formula would I have to use. I used nested IFs, but it doesn't go further than 5 ( i actually have more than 1-3 and x-z. It's more like 1-26, a-z).
Thanks in advance..
If the numbers in A1, A2, A3 are whole numbers and 1=x,2=y,3=z then you can create an easy vlookup formula by creating a table with x,y,z and next to each letter put the whole number that correspond to those letters and then just use vlookup, that looks up A1,A2,A3, etc against the table and spits out a letter.
Yes, but vlookup matches exact data. I need the vlookup formula to use a "less than or equal to" argument. Do you know how I might be able to do this?
can you just find the least value from table 2 that A1 is less than or equal (usin array formula), and then use that to just do a vlookup off table 2
so say second table starts in D1, then
{=VLOOKUP(MIN(IF(A1=$D$1:$D$3,$D$1:$D$3)),$D$1:$E$3,2,FALSE)}
Getting value error..
did you hold down ctrl+shift before you press enter to make it array? works for me
by input you mean output?
A1 B1 C1 A2 B2 C2 A3 B3 C3
A5 B5 A6 B6 A7 B7 where A5 has 1,B5 has x, etc.
type in A9: =vlookup(A1,$A$5:$B$7,2,0) there are other ways of doing it but quick n dirty vlookup suffices here according to your logic since you can have the cell value equal
$A$5:$B$7 = cell range 1 to z, you didn't specify
I would have used the following :
=IF(A1=1;"x";IF(AND(A1>1;A1=2);"y";IF(AND(A1>2;A1=3);"z";0)))
But I don't get how it is limited to 5 nested IFs ... I'm on a model that uses 10 and I have never had a single problem.
is this resolved? you can script this easily in vba and get a much quicker output.
Sorry, I had to quickly work on something else. I'm back now.
What I did was use a nested IF for 1-5, and 0, and another nested IF for 5 - the rest. Then I took the MAX between the two. It definitely works.
Now I'm faced with another problem.. My data now looks something like this
(original data) A1 A2 A3 B1 B2 B3 C1 C2 C3
(outputs from my previous question) z (from A1) y (from A2) x (from A3) x (from B1) z (from B2) y (from B3) y (from C1) x (from C2) z (from C3)
from means that that cell is pulling data from A1 - C3
What I want to do now is fill in the following:
x Sum all in column 1 from A1:C3 that corresponds to x / Count all in column 1 from A1: C3 that corresponds to x. y Same, but for y z Same, but for z
Is this clear? I feel like this should be simple, but I'm confusing myself..
To give more clarity, say I have a bunch of stocks. I have data for each stock and how they performed for each year (2001-2012). I want to show a chart that shows the average annual performance of the stocks each year, by DECILE.
So, first I made some decile thresholds which was, in my previous example:
1 x 2 y 3 z
And then I split the original stock data:
2001 2002 2003stock 1 A1 A2 A3 stock 2 B1 B2 B3 stock 3 C1 C2 C3
(which is what you saw in my previous example as well)
into each decile by using the nested IF.
Now I have
2001 2002 2003stock 1 x y z stock 2 y x y stock 3 z z x
But I want to get the average annual performance of all the stocks, divided by decile (x,y,z)
am I being clear enough?? let me know if I'm not..
T
Ut non doloribus accusantium modi et officiis magni. Non quam qui mollitia.
Repellendus voluptatem rem aliquid nihil qui eos esse in. Omnis porro placeat est fuga error blanditiis iure. Officia sit perspiciatis ut autem illo.
See All Comments - 100% Free
WSO depends on everyone being able to pitch in when they know something. Unlock with your email and get bonus: 6 financial modeling lessons free ($199 value)
or Unlock with your social account...