# Solved: Excel 2007 - Dynamic formula based on Data Validation List

Discussion in 'Business Applications' started by mhutton1, Dec 14, 2012.

Not open for further replies.

Joined:
Dec 14, 2012
Messages:
5
Hi all,

I am trying to develop a cost calculator based on several drop down lists. I have used the following formula:

=AVERAGEIFS('Scope Of Work'!I:I,'Scope Of Work'!C:C,C6,'Scope Of Work'!F:F,'Cost Calculation'!D6,'Scope Of Work'!G:G,'Cost Calculation'!E6)

Cells C6, D6 and E6 in the Cost Calculation Tab are all drop down menus based on data validation. The formula works when all 3 cells have a selection value but I also want it to calculate if only one or two of the drop down lists have been populated, any ideas?

Thanks,

Mark.

2. ### etafModerator

Joined:
Oct 2, 2003
Messages:
64,891
First Name:
Wayne
Can you post a sample dummy spreadsheet
Blanks should not be calculated - unless the dropdown is being seen as a zero, and then perhaps an array with <> 0 may be able to work

Joined:
Dec 14, 2012
Messages:
5
Hi etaf, thanks for getting back to me. I've uploaded a simple version of the worksheet I am creating. All the formulas etc. are the same.

as you can see on the calculation page there are 3 dropdowns. How do I still calculate if one or two of the cells are left blank?

Thanks again,

Mark.

File size:
10.7 KB
Views:
53
4. ### etafModerator

Joined:
Oct 2, 2003
Messages:
64,891
First Name:
Wayne
so a blank criteria is not found and so you dont get the average calculation

there maybe an easy way to do this , I can only think of a nested set of IF statements to test for each possible combination of criteria

on a truth table - you can have quite a lot of variations

A-B-C
0-0-0
0-0-1
0-1-0
0-1-1
1-0-0
1-0-1
1-1-0
1-1-1

so we would need to tests for all the zeros and calc - sure theres a simpler way to do that - maybe with an array formula or by using SUM and countifs

i will see if anyone else answers before doing a complicated nested if

Joined:
Dec 14, 2012
Messages:
5
OK thanks, i'll have a play too and see if I can come up with anything.

6. ### etafModerator

Joined:
Oct 2, 2003
Messages:
64,891
First Name:
Wayne
your drop down does not accept blanks
so what do you want to do if one of the dropdowns criteria does not exist

for example
Complex New Product Amendments

Joined:
Dec 14, 2012
Messages:
5
Hi etaf, I will include a blank criteria. But if one is blank, I want it to be ignored meaning the average is only calculated taking into account the remaining two criteria.

I've been working on this for some time but I'm not confident it's possible anymore.

Thanks again.

8. ### etafModerator

Joined:
Oct 2, 2003
Messages:
64,891
First Name:
Wayne
not sure if this comes anywhere near - see the code box below for the actual code

This is how I broke it down
=IF( AND (b3="",C3="") ,

=IFERROR(AVERAGEIFS(Data!D,Data!C:C,'Calculation page'!D3),0) ,

=IF( AND (b3="",D3="") ,

=IFERROR(AVERAGEIFS(Data!D,Data!B:B,'Calculation page'!C3),0),

=IF( AND (C3="",D3="") ,

=IFERROR(AVERAGEIFS(Data!D,Data!A:A,'Calculation page'!B3),0),

=IF( b3="" ,

=IFERROR(AVERAGEIFS(Data!D,Data!B:B,'Calculation page'!C3,Data!C:C,'Calculation page'!D3),0),

=IF( C3="" ,

=IFERROR(AVERAGEIFS(Data!D,Data!A:A,'Calculation page'!B3,Data!C:C,'Calculation page'!D3),0),

=IF( D3="" ,

=IFERROR(AVERAGEIFS(Data!D,Data!A:A,'Calculation page'!B3,Data!B:B,'Calculation page'!C3),0)

Code:
`=IF(AND(B3="",C3=""),IFERROR(AVERAGEIFS(Data!D:D,Data!C:C,'Calculation page'!D3),0),IF(AND(B3="",D3=""),IFERROR(AVERAGEIFS(Data!D:D,Data!B:B,'Calculation page'!C3),0),IF(AND(C3="",D3=""),IFERROR(AVERAGEIFS(Data!D:D,Data!A:A,'Calculation page'!B3),0),IF(B3="",IFERROR(AVERAGEIFS(Data!D:D,Data!B:B,'Calculation page'!C3,Data!C:C,'Calculation page'!D3),0),IF(C3="",IFERROR(AVERAGEIFS(Data!D:D,Data!A:A,'Calculation page'!B3,Data!C:C,'Calculation page'!D3),0),IF(D3="",IFERROR(AVERAGEIFS(Data!D:D,Data!A:A,'Calculation page'!B3,Data!B:B,'Calculation page'!C3),0),IFERROR(AVERAGEIFS(Data!D:D,Data!A:A,'Calculation page'!B3,Data!B:B,'Calculation page'!C3,Data!C:C,'Calculation page'!D3),0)))))))`

Joined:
Dec 14, 2012
Messages:
5
Wow! What a formula, I had no idea it would be that complex, thank you so much etaf. I would never have been able to figure it out.

10. ### etafModerator

Joined:
Oct 2, 2003
Messages:
64,891
First Name:
Wayne
you do need to test it out thoroughly - As i did not - also of course some combinations return zero anyway, so you may want to make a note of those first

any issues let us know

As Seen On