AverageIfs Problem in EXCEL 97

hdbkiwi

New Member
Joined
Sep 22, 2014
Messages
10
*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:

SizeTypeOrientation
25FlatSW

<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)TypePositionHeating Requirement (kWh)
100FlatAble 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)TypePositionHeating requirement (kWh)
100FlatNW1621.5
100FlatSW1519.2
400HouseS5012.1
100FlatSW1991.2

<tbody>
</tbody>

 

Some videos you may like

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,133
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
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

New Member
Joined
Sep 22, 2014
Messages
10
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.
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,123,123
Messages
5,599,834
Members
414,341
Latest member
Mohammedsobhey

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top