AverageIf Function giving unexpected result

G

GREGRACH

New Member
Joined
Mar 8, 2017
Messages
19
Office Version
  1. 365
Platform
  1. MacOS
Hi All,

I have the following formula: =AVERAGEIF(E9:E134,"<>"&TODAY(),F9:F134)

It is taking information from the following:

Date Received....Date Invoiced (E)...Claim Life (F)
1 March............14 March .............. 13
1 March............6 March............... 5
1 Feb................14 March.............. 41
1 Jan................10 March.............68

So the formula is supposed to give me the average claim life of the above rows in bold text... (dates of 14 March in column E are using the formula =TODAY() so anything with today's date is excluded )... but the formula is calculating 0.6 instead of 36.5. Please note, claim life (column F) is also a formula =DATEDIF(D9,E9,"d") so I'm not sure if that has anything to do with it?

Where am I going wrong? I have a similar formula that works fine but this one does not....

Any help would be hugely appreciated! Thank you.
 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.
Sort by date Sort by votes
Based on the sample you provided, I get 31.75...
E​
F​
G​
H​
I​
8​
Date ReceivedDate Invoiced (E)Claim Life (F)
9​
1-Mar
14-Mar​
13​
31.75​
10​
1-Mar
6-Mar​
5​
11​
1-Feb
14-Mar​
41​
12​
1-Jan
10-Mar​
68​
I9=AVERAGEIF(E9:E134,"<>"&TODAY(),G9:G134)
 
Upvote 0
I'm getting 36.5 as my average

(I think the OP and I are in a different time zone to Ford as his 31.75 is the average of all the col F entries)

suggest your check for 2 things
- "date received" and "date invoiced" . you are only showing day and month no year.. check are all the years 2017
- your shot is showing data row rows 9 to 12 (f9 = 13 thru to f12 = 68) but are there any more rows between row 12 and row 134 with data
 
Last edited:
Upvote 0
Yes that is the average for all rows but I am trying to exclude rows 9 & 11... so just adding up numbers in columns G 10 and 12. So I want my formula to exclude the rows that have today's date in the invoiced column (F) and only average the other rows. I have similar formulas which work great but for some reason this formula is returning 0.6 so I figure it is a problem with the formula but for the life of me, I can't work out what is wrong with the formula.



FDibbins said:
Based on the sample you provided, I get 31.75...

E​

F​

G​

H​

I​

8​
Date Received
Date Invoiced (E)
Claim Life (F)

9​
1-Mar

14-Mar​

13​

31.75​

10​
1-Mar

6-Mar​

5​

11​
1-Feb

14-Mar​

41​

12​
1-Jan

10-Mar​

68​

<tbody>
</tbody>

I9=AVERAGEIF(E9:E134,"<>"&TODAY(),G9:G134)
Click to expand...
 
Upvote 0
Yes, there are more rows (I just typed an example) but some rows have no data.... ahhh so yes, it is averaging all rows at the moment by the looks of it. How do I get it to only average rows with data?


liveinhope said:
I'm getting 36.5 as my average

(I think the OP and I are in a different time zone to Ford as his 31.75 is the average of all the col F entries)

suggest your check for 2 things
- "date received" and "date invoiced" . you are only showing day and month no year.. check are all the years 2017
- your shot is showing data row rows 9 to 12 (f9 = 13 thru to f12 = 68) but are there any more rows between row 12 and row 134 with data
Click to expand...
 
Upvote 0
GREGRACH said:
It's March 14 in Australia
Click to expand...

haha OK, you don't have a location posted. Still Mar 13 in USA

I changes some dates to test...
E​
F​
G​
H​
I​
8​
Date ReceivedDate Invoiced (E)Claim Life (F)
9​
1-Mar
13-Mar​
13​
36.5​
10​
1-Mar
6-Mar​
5​
11​
1-Feb
13-Mar​
41​
12​
1-Jan
10-Mar​
68​
I9=AVERAGEIF(F9:F134,"<>"&TODAY(),G9:G134)

hard to say what the problem your end might be, without seeing more data - but even if you posted it here, still may not show what the problem might be. Are you sure that the 2 dates for today, are really dates?
Test with =ISNUMBER(cell-ref)

Also, manually type in the data again, and make sure the cell is formatted as date?
 
Upvote 0
Yes it's a weird one. They are definitely dates, I checked the formatting and retyped over the formulas so the claim life were numbers, not formulas. I also retyped over the dates and checked they were formatted properly which they were.

Thanks so much anyway!
 
Upvote 0
OK... so if I take out the formula working out the number of days (column G) in the rows without data, the formula works?! As soon as I put that formula back, it doesn't work. So it's happy when there is no formula in column G only. Any ideas why this might be?



FDibbins said:
haha OK, you don't have a location posted. Still Mar 13 in USA

I changes some dates to test...

E​

F​

G​

H​

I​

8​
Date Received
Date Invoiced (E)
Claim Life (F)

9​
1-Mar

13-Mar​

13​

36.5​

10​
1-Mar

6-Mar​

5​

11​
1-Feb

13-Mar​

41​

12​
1-Jan

10-Mar​

68​

<tbody>
</tbody>

I9=AVERAGEIF(F9:F134,"<>"&TODAY(),G9:G134)

hard to say what the problem your end might be, without seeing more data - but even if you posted it here, still may not show what the problem might be. Are you sure that the 2 dates for today, are really dates?
Test with =ISNUMBER(cell-ref)

Also, manually type in the data again, and make sure the cell is formatted as date?
Click to expand...
 
Upvote 0
You must log in or register to reply here.

Similar threads

F
  • Question
Conditional format based on time
Replies
2
Views
76
Fegal
F
K
  • Question
AVERAGEIFS / SUMIF with multiple criteria
Replies
3
Views
813
arthurbr
arthurbr
D
  • Solved
Timesheet Calculation
Replies
7
Views
752
jorismoerings
J
A
  • Solved
AVERAGEIFS with 4 criteria
2
Replies
16
Views
303
Fluff
Fluff
R
  • Solved
Connect periods with spilled result, where the result is same as the calculated result in previous row
2
Replies
15
Views
152
roelandwatteeuw
R

Forum statistics

Threads
1,214,593
Messages
6,120,435
Members
448,961
Latest member
nzskater

Share this page

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
Back
Top