Hi, i have a problem, im trying to calculate the difference between 2 dates in 2 columns and displaying the total average of these days.
For example:
A B
12-05-2015 30-05-2015
14-05-2016 30-05-2016
Average days between column A and B = y
All in one single formula.
please help...
Try this:
Excel 2016 (Windows) 32 bit
Sheet: Sheet1
Excel 2016 (Windows) 32 bit
Sheet: Sheet1
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
Depends on your worksheet set up. But you can do something like below.
=SUMPRODUCT((B1:B2)-(A1:A2))/COUNT(A:A)
gives me a negative number, maybe because of the empty cells? Im trying to do the entire column, so that it updates the data when a new line is added
The column B may have empty cells
Last edited by rukazer; 01-31-2017 at 11:03 AM.
So, give us some truly representative data to work with!
Assuming you don't want to include rows with blanks in B:
Excel 2016 (Windows) 32 bit
Sheet: Sheet1
Thank you AliGW, that was brilliant, worked like a charm! =)
You're welcome! Just got my logic cap on ...
one more thing, following your formula
=(SUM(B10:B12)-SUMIF(B10:B12,"<>",A10:A12))/COUNT(B10:B12)
I want to create 2 cells, Initial Date and End Date. And by changing the dates on these cells, i wanted to show only the results between these dates, of the formula you created.
I know im a being a pain, but my excel is really rusty, please help... :|
Try this (untested, as I have only just got home from work):
=(SUMIFS(B10:B12,A10:A12,">="&A1,B10:B12,"<="&B1)-SUMIFS(A10:A12,A10:A12,">="&A1,B10:B12,"<="&B1,B10:B12,"<>"))/COUNT(B10:B12)
Formula:
Please Login or Register to view this content.
This is the formula im using (its in Portuguese) but the SUMIFS is the SOMA.SE.S and the COUNT is the CONTAR, C5 is the Initial Date and D5 the End Date.
The formula gave me a large number like 459663,65. The average days should be around 12,1. When i change the dates from the cells C5 and D5 it also changes the result, but not close to the correct one.
Nevermind, i found the problem! Your formula was flawless!
That's good to know. Glad to have helped!
On a closer look the values don't add up, the average should be 3,25 and the formula gives me 3,36. Also when i change the dates ate the bottom the Formula Result gives wierd numbers.
Sorry for not posting an attachment but i think the function is not working.
The formula
Please Login or Register to view this content.
Please Login or Register to view this content.
Last edited by rukazer; 01-31-2017 at 01:58 PM. Reason: forgot code
Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.
Based on your posted sample data the correct result is 3.35714285714286 or rounded to 2 decimal places 3.36.Originally Posted by rukazer
All of your data falls within your date range of 01/01/2016 - 31/01/2017.
Biff
Microsoft MVP Excel
Keep It Simple Stupid
Let's Go Pens. We Want The Cup.
Ok thanks im going to take a better look tomorrow at work
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks