Dependant Formula Lookup

[rl424]

Hi all,

I have become completely stuck so thought i'd see if anyone on here could help.

I'm not sure quite what the easiest way to do this would be but im trying to create a spreadsheet for others to use where they would select from dropdowns and enter a value to generate an output value.

this is the scenario simplified slightly.

A B C D
list list
1 dB 1 User input value
2 mm 2
3 3
4 4
5 5
6 6

In column D I want to calculate a value using a formula and the inputted value in C. Which formula to be used depends on what is selected in A and B. There are two formula sets dependant on whther dB or mm is selected, and which formula in the chosen set is dependant on which number is selected in B. Does anyone know the soloution or any way of doing something similar?

Thanks

Rhys

 

[Andrew Poulsom]

Welcome to MrExcel.

What would the formulas be for dB and mm respectively?

 

[rl424]

Theres a total of 34 different formula's all of which are in the format =ax^n. Two for each number from 2 to 10 in intervals of 0.5, and there are two variations of each these formulas depending on whether mm or dB is chosen.

Rhys

 

[Andrew Poulsom]

Sorry, I can't help you unless you are more specific.

 

[rl424]

Ok... well the spreadsheet is for calculating the cost of compressed air leakage in a system. From surveys, either the size of the hole will be taken (mm) or the leakage rate (dB). The compressed air line can be at a range of different pressures. 2.0,2.5,3.0,......10.0 (Bars). In order to calculate anything related to the air leak a the leakage rate or hole size needs to be converted to a common term (CFM). So after someone has completed their air leak survey they need to be able to enter either the leakage rate or hole size and the pressure of the system and the rest will be calculated within the spreadsheet. there is a different formula for every single pressure, and that formula is also different depending on whether the user has entered a hole size or leakage rate. So in the CFM cell i need to refer to the previous two cell choices (dB or mm and Pressure of the system) to decide which formula from a table on another sheet to use within that cell.

Hope this makes it clearer.

Regards

Rhys

 

[Andrew Poulsom]

Hope this makes it clearer.

Not really.