Help : Excel fundis/experts

[LancelotSA]

Hey guys,

I have a Excel spreadsheet that I am needing help with.

I am allowing the user to input three values : size, height and quantity. So I have three variables. I then need to use these three values together to cross reference the price. So in other words using the table below if the user input values of Size = 25 Height = 2 and Quantity = 3 I would want the spread sheet to return the answer 6.42

How do I do that?

AND, IFs do not work as it limits the number you can have and obviously with three variables you have multiple possible solutions.


Size Height 1 OF 2 OF 3 OF 4 OF 5 OF
15 1 7.23 4.34 3.61 2.89 2.17
25 1 12.85 7.71 6.42 5.14 3.85
35 1 12.95 7.77 6.48 5.18 3.89
45 1 14.45 8.67 7.23 5.78 4.34
55 1 18.88 11.33 9.44 7.55 5.66
65 1 25.69 15.42 12.85 10.28 7.71
75 1 37.00 22.20 18.50 14.80 11.10
85 1 34.78 20.87 17.39 13.91 10.43
95 1 55.50 33.30 27.75 22.20 16.65
105 1 55.50 33.30 27.75 22.20 16.65
115 1 65.78 39.47 32.89 26.31 19.73
125 1 78.93 47.36 39.47 31.57 23.68
135 1 85.51 51.31 42.76 34.20 25.65
145 1 92.09 55.25 46.04 36.84 27.63
155 1 129.50 77.70 64.75 51.80 38.85
165 1 135.98 81.59 67.99 54.39 40.79
175 1 138.57 83.14 69.28 55.43 41.57
185 1 142.45 85.47 71.23 56.98 42.74
195 1 148.93 89.36 74.46 59.57 44.68
205 1 155.40 93.24 77.70 62.16 46.62
215 1 161.88 97.13 80.94 64.75 48.56
225 1 168.35 101.01 84.18 67.34 50.51
235 1 174.83 104.90 87.41 69.93 52.45
245 1 259.00 155.40 129.50 103.60 77.70
15 2 7.23 4.34 3.61 2.89 0.00
25 2 12.85 7.71 6.42 5.14 0.00
35 2 12.95 7.77 6.48 5.18 0.00
45 2 14.45 8.67 7.23 5.78 0.00
55 2 18.88 11.33 9.44 7.55 0.00
65 2 25.69 15.42 12.85 10.28 0.00
75 2 37.00 22.20 18.50 14.80 0.00
85 2 34.78 20.87 17.39 13.91 0.00
95 2 55.50 33.30 27.75 22.20 0.00
105 2 55.50 33.30 27.75 22.20 0.00
115 2 65.78 39.47 32.89 26.31 0.00
125 2 78.93 47.36 39.47 31.57 0.00
135 2 85.51 51.31 42.76 34.20 0.00
145 2 92.09 55.25 46.04 36.84 0.00
155 2 129.50 77.70 64.75 51.80 0.00
165 2 135.98 81.59 67.99 54.39 0.00
175 2 138.57 83.14 69.28 0.00 0.00
185 2 142.45 85.47 71.23 0.00 0.00
195 2 148.93 89.36 74.46 0.00 0.00
205 2 155.40 93.24 77.70 0.00 0.00
215 2 161.88 97.13 80.94 0.00 0.00
225 2 168.35 101.01 84.18 0.00 0.00
235 2 174.83 104.90 87.41 0.00 0.00
245 2 259.00 155.40 129.50 0.00 0.00
15 3 7.23 4.34 3.61 0.00 0.00
25 3 12.85 7.71 6.42 0.00 0.00
35 3 12.95 7.77 6.48 0.00 0.00
45 3 14.45 8.67 7.23 0.00 0.00
55 3 18.88 11.33 9.44 0.00 0.00
65 3 25.69 15.42 12.85 0.00 0.00
75 3 37.00 22.20 18.50 0.00 0.00
Etc etc down to height of six

Thanks

[DJ...]

Split the tables up, then do if statements and vlookups. Should work fine. If height = 1, then refer to table 1, then do a vlookup for the values...

[LancelotSA]

Split the tables up, then do if statements and vlookups. Should work fine. If height = 1, then refer to table 1, then do a vlookup for the values...

I originally tried with different worksheets for each height value (there are only six options) and then attempted to use lookup to specify the use of that worksheet and then lookup the size value on there to then cross reference the price for quantity but it just did not work out as easily as it sounds.

My previous attempt involved IF statements for the size but there are too many options for Excels liking. It limits the number of IFs and ANDs.

May I ask you for the exact formula you'd use for your suggestion? Sorry, I know it is Sunday! ;)

[feo]

DJ... is right. One possible solution would be to have 3 different tables, one for each height. Then, the height the user has input would determine which of the 3 tables will be used in the lookup. The size would determine the actual lookup value and the quantity would be the reference column in the VLOOKUP.

[DJ...]

Yip, it's a Sunday, I'm watching a movie, posting on the net and going over reports before Monday morning meetings. Likelihood of me putting this into excel and figuring out the exact formula is slim to none. I'd start with splitting the ranges and creating an if formula to determine which range to reference, then another if formula to determine which column to reference. From there it should be simple. But I haven't given it a shot yet so I can't say for sure...

[air]

www.mrexcel.com
has helped me a number of times.

[LancelotSA]

Thanks guys. But what formula do I use to tell excel which table to use? And I assume by table you are meaning that I should have each height option on a different worksheet?

So if the user inputs height of 1. I now want excel to only consider worksheet 1 which contains the table for all height 1 options. How do I tell it to do this?

If you can give me formulas it'd be much appreciated because I think I have fried my brain doing this! ;)

[feo]

Thanks guys. But what formula do I use to tell excel which table to use? And I assume by table you are meaning that I should have each height option on a different worksheet?

So if the user inputs height of 1. I now want excel to only consider worksheet 1 which contains the table for all height 1 options. How do I tell it to do this?

If you can give me formulas it'd be much appreciated because I think I have fried my brain doing this! ;)
It's done, I'll upload some screenshots and an explanation for you in a minute.

[LancelotSA]

It's done, I'll upload some screenshots and an explanation for you in a minute.

Ha ha ha

[feo]

Ha ha ha

No wonder you're so popular on these forums. :sick:

Anyway..

Split the tables based on height, name them Table1, Table2 etc.

Name the input cells Size, Height etc..

Then use the formula in the screenshot..

http://farm4.static.flickr.com/3448/3736262740_df3a9c5ffd_b.jpg

http://farm3.static.flickr.com/2486/3736262986_dd7961976d.jpg?v=0

[LancelotSA]

No wonder you're so popular on these forums. :sick:

Well to be honest I assumed you were joking hence the laughter! ;) But it's great to know I am so popular.

Thank you very much for your response, I'll work through it now.

[LancelotSA]

Damn! I am doing exactly what you are doing feo but it is just not working for me. I keep getting a #NAME? error!

Could it be because I am using office 2007? It should still work. The "insert table" function seems to work differently though.

[LancelotSA]

Got it working! Thank you very much all and in particular Feo!