Excel Function Question

[tonyh29]

Any Excel wizards here  ( @BOF ?)

I'm constantly asked to produce Quote grids they often equate to 300 different permutations , none of which bare any resemblance to anything I then actually run for these clients

What I do have though is historical  cost data of work I have  run for them which would allow me to produce a fairly reasonable Cost per interview (CPI)

So on one worksheet tab  I'll have different Test types  , along with a CPI value  , for each test type which I can trim down to around 10 categories

then on the front page the client can enter their variables 

 

So a user can Enter

Test Type  (via Data Validation lookup pull down)

Number of people (N)

Number of Samples (S)

 

and I'd like it to look at the Data and say  , OK my test type is x and the CPI for x is £xx.xx  .. which I can then multiply by  N to come up with a cost

The number of samples would also play a part my calculations but I don't want to run before I can walk   

 

Am I seeking something that can't be done , or is that fairly straightforward stuff for Excel ... SUMIF seemed to be a good start point , but I'm not sure it meets my criteria ?

Edited January 18, 2018 by tonyh29

[limpid]

Is the title swearing? 

[blandy]

if {value} = Yes, else [DHUTWU]

[snowychap]

 

@tonyh29

I guess you got this sorted as it's from a while ago but if not:

If your historical data is summed up in to a single table with CPI per Test Type then you just need a simple vlookup using the input Test type from the relevant cell multiplied by the other two variables.

If you've got a load of historical data then if you want a simple average, just use the averageif function for each test type.

Put your test type in one cell (e.g. A2 on a new sheet) and then in b2: =averageif(range,a2,average_range) where range is the column range in the historical data with the test types and average_range is the column range in the historical data with the values.