AverageIfs Problem in EXCEL 97

[hdbkiwi]

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

[TABLE="width: 200"]
<tbody>[TR]
[TD]Size[/TD]
[TD]Type[/TD]
[TD]Orientation[/TD]
[/TR]
[TR]
[TD]25[/TD]
[TD]Flat[/TD]
[TD]SW[/TD]
[/TR]
</tbody>[/TABLE]

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:
[TABLE="width: 500"]
<tbody>[TR]
[TD]Size(square meters)[/TD]
[TD]Type[/TD]
[TD]Position[/TD]
[TD]Heating Requirement (kWh)[/TD]
[/TR]
[TR]
[TD]100[/TD]
[TD]Flat[/TD]
[TD]Able to put in "N, NW, etc" but also able to be left blank[/TD]
[TD]=AVERAGEIFS(('Database '!D1:D100,'Database '!A1:A100,'Input sheet'!A2,'Database '!B1:B100,'Input sheet'!B2),'Database '!C1:C100,'Input sheet'!C2)[/TD]
[/TR]
</tbody>[/TABLE]


Database Worksheet (with existing buildings)
[TABLE="width: 500"]
<tbody>[TR]
[TD]Size (square meters)[/TD]
[TD]Type[/TD]
[TD]Position[/TD]
[TD]Heating requirement (kWh)[/TD]
[/TR]
[TR]
[TD]100[/TD]
[TD]Flat[/TD]
[TD]NW[/TD]
[TD]1621.5[/TD]
[/TR]
[TR]
[TD]100[/TD]
[TD]Flat[/TD]
[TD]SW[/TD]
[TD]1519.2[/TD]
[/TR]
[TR]
[TD]400[/TD]
[TD]House[/TD]
[TD]S[/TD]
[TD]5012.1[/TD]
[/TR]
[TR]
[TD]100[/TD]
[TD]Flat[/TD]
[TD]SW[/TD]
[TD]1991.2[/TD]
[/TR]
</tbody>[/TABLE]

 

[RoryA]

Welcome to the forum.

If there's always data in the Position column in the database sheet, you could use:
=AVERAGEIFS(('Database '!D1:D100,'Database '!A1:A100,A2,'Database '!B1:B100,B2),'Database '!C1:C100,IF(C2="","*",C2))

Where does Excel 97 come into it?

 

[hdbkiwi]

Welcome to the forum.

If there's always data in the Position column in the database sheet, you could use:
=AVERAGEIFS(('Database '!D1:D100,'Database '!A1:A100,A2,'Database '!B1:B100,B2),'Database '!C1:C100,IF(C2="","*",C2))

Where does Excel 97 come into it?

Thank you for the welcome RoryA! And cheers for responding.

Agh, sorry about that. I meant Excel 2007.

Yes, there is always data in the database for position. I tried using the adjusted formula above; unfortunately when leaving C2 blank the output still comes out as #DIV/0.