AverageIfs Problem in EXCEL 97

hdbkiwi

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

 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>

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

RoryA

MrExcel MVP, Moderator
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
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:

Replies
5
Views
210
Replies
10
Views
677
Replies
0
Views
339
Replies
8
Views
578
Replies
4
Views
2K

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,163,706
Messages
5,833,238
Members
430,198
Latest member
KitaYama

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.

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

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