*sorry if duplicate post, browser crashed and I didn't see that it had posted but on the off chance it did apologies*
Dear all,
First off, thank you in advance to anyone who posts an answer to this question - I really appreciate your help!
I am using Excel 2007 to try and make a spreadsheet that will try and estimate the energy usage of a home based on a user inputting their criteria and matching it to a database.
For example, the user below would enter the size of unit they are proposing to build, the type of unit (flat, semi-detached house, etc) and orientation of building (North, North East, etc). Then, to work out the, say, heating requirement, I would use AVERAGEIFS to link to a database that pulled out and averaged all of the relevant flats that had the matching inputs. Say I have the example below:
<tbody>
</tbody>
I use the =AVERAGEIFS (average_range,SIZE_range1,criteria1,TYPE_range2,criteria2, ORIENTATION_range3, criteria3) to pull out the average energy use of all the Flats that match that size and orientation.
That so far works and I am happy. My problem is this:
Say that the developer doesn't know what orientation his building will be, so he decides to leave it blank. However, =AVERAGEIFS expects a value for "criteria 3" and when there is a blank value will return "#DIV/0! How do I let the developer leave certain criteria blank such that Excel will just use the other criteria to return a relevant value? I thought about using Nested Ifs but I kept coming up with various errors. Is this the right strategy?
Again, thank you for considering this problem and helping me through it - your help is much appreciated. For the sake of being more comprehensive, I have included my actual data below.
Worksheet with input and output:
<tbody>
</tbody>
Database Worksheet (with existing buildings)
<tbody>
</tbody>
Dear all,
First off, thank you in advance to anyone who posts an answer to this question - I really appreciate your help!
I am using Excel 2007 to try and make a spreadsheet that will try and estimate the energy usage of a home based on a user inputting their criteria and matching it to a database.
For example, the user below would enter the size of unit they are proposing to build, the type of unit (flat, semi-detached house, etc) and orientation of building (North, North East, etc). Then, to work out the, say, heating requirement, I would use AVERAGEIFS to link to a database that pulled out and averaged all of the relevant flats that had the matching inputs. Say I have the example below:
| Size | Type | Orientation |
| 25 | Flat | SW |
<tbody>
</tbody>
I use the =AVERAGEIFS (average_range,SIZE_range1,criteria1,TYPE_range2,criteria2, ORIENTATION_range3, criteria3) to pull out the average energy use of all the Flats that match that size and orientation.
That so far works and I am happy. My problem is this:
Say that the developer doesn't know what orientation his building will be, so he decides to leave it blank. However, =AVERAGEIFS expects a value for "criteria 3" and when there is a blank value will return "#DIV/0! How do I let the developer leave certain criteria blank such that Excel will just use the other criteria to return a relevant value? I thought about using Nested Ifs but I kept coming up with various errors. Is this the right strategy?
Again, thank you for considering this problem and helping me through it - your help is much appreciated. For the sake of being more comprehensive, I have included my actual data below.
Worksheet with input and output:
| Size(square meters) | Type | Position | Heating Requirement (kWh) |
| 100 | Flat | Able to put in "N, NW, etc" but also able to be left blank | =AVERAGEIFS(('Database '!D1:D100,'Database '!A1:A100,'Input sheet'!A2,'Database '!B1:B100,'Input sheet'!B2),'Database '!C1:C100,'Input sheet'!C2) |
<tbody>
</tbody>
Database Worksheet (with existing buildings)
| Size (square meters) | Type | Position | Heating requirement (kWh) |
| 100 | Flat | NW | 1621.5 |
| 100 | Flat | SW | 1519.2 |
| 400 | House | S | 5012.1 |
| 100 | Flat | SW | 1991.2 |
<tbody>
</tbody>
