1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

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

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

Thread Status:
Not open for further replies.
Advertisement
  1. mhutton1

    mhutton1 Thread Starter

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

    etaf Moderator

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

    mhutton1 Thread Starter

    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.
     

    Attached Files:

  4. etaf

    etaf Moderator

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

    mhutton1 Thread Starter

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

    etaf Moderator

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

    mhutton1 Thread Starter

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

    etaf Moderator

    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: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)

    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)))))))
     
  9. mhutton1

    mhutton1 Thread Starter

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

    etaf Moderator

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

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1080878

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice