Help : Excel fundis/experts

L

LancelotSA

Banned
Joined
Aug 20, 2007
Messages
14,713
Reaction score
5
Location
Stunning Africa
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.

HTML: 
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
 
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...
 
DJ... said:
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...
Click to expand...

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! ;)
 
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.
 
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...
 
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! ;)
 
LancelotSA said:
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! ;)
Click to expand...
It's done, I'll upload some screenshots and an explanation for you in a minute.
 
LancelotSA said:
Ha ha ha
Click to expand...

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..

3736262740_df3a9c5ffd_b.jpg


3736262986_dd7961976d.jpg
 
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.
 
You must log in or register to reply here.
Back
Top
Sign up to the MyBroadband newsletter
X