Hi,
In the attached spreadsheet, cell F3, I need to create a formula that will return the average daily balance for type="Equity Debt/Credit" amount. I need the ability to do this within a date range placed in F1 & F2.
Thanks in advance for the help and Merry Christmas
-Manny
Merry Christmas.
Give this a try:
Formula:
Please Login or Register to view this content.
BSB
Thanks. The formula isn't returning the correct value.
The formula needs to add up the daily balance between two periods and average the total by the periods between F1 and F2 to get the average daily balance balance. I've attached a updated spreadsheet to the stream for example.
Thanks
-Manny
How are you defining 'average daily credit'?
The average of the 14 Equity Debt/Credit numbers is $281.07
Richard Buttrey
RIP - d. 06/10/2022
If any of the responses have helped then please consider rating them by clicking the small star iconbelow the post.
I'm after the average daily balance in the account between two periods. Not the averages of the equity debt/credits between two periods.
Sorry that doesn't really help. I need to understand your actual calculation which produces the result you require. i.e. show the cell refs and values and explain the arithmetic used.
The nearest I can get is $3136.14 using a helper column D
=($F$2-A2)*C2/66
then
SUMIFS(D:D,B:B,"Equity Debt/Credit",A:A,">="&$F$1,A:A,"<="&$F$2)
[.... withdrawn .... I misread: I thought column C is the balance on the date in colulmn A]
Last edited by curiouscat408; 12-25-2021 at 07:54 PM.
And to that end, it would help to create a column (D) with the daily balance. I believe that is:Originally Posted by Prof Sick
D2: =C2
D3: =D2+C3
Then, the simple calculation of the average daily balance between 1/1/2007 and 1/25/2007 is:
=(SUMPRODUCT(A4:A13 - A3:A12, D3:D12) + D13) / (F2 - F1 + 1)
To allow for variable dates in F1 and F2, the following formulas would help:
G1: =MATCH(F1, $A$1:$A$1000, 0)
G2: =MATCH(F2, $A$1:$A$1000, 0)
Then the formula in F3 can be:
Generally, INDEX:INDEX is better because OFFSET is a "volatile" function.
Please Login or Register to view this content.
We can confirm the result by creating a table in J1:J25 with the dates between 1/1/2007 and 1/25/2007, and enter the following formula into K1:K25:
K1: =VLOOKUP(J1, $A$2:$D$16, 4, 1)
Then the average daily balance is:
=SUM(K1:K25) / (F2 - F1 + 1)
Last edited by curiouscat408; 12-25-2021 at 07:57 PM.
I overlooked that.
"Great minds think alike".
But your ADB is 3148.06 (3148.05970149254; you entered 3148.059), whereas my ADB is 3148.09 (3148.0895522388).
I believe the difference is due to mistakes in your column M, which you entered manually instead of using a VLOOKUP formula, as I do.
Specifically:
for 1/3/2007: you have 0; should be -3
for 1/21/2007: you have 0; should be 7
Last edited by curiouscat408; 12-26-2021 at 03:43 AM.
Thanks for everyone's help on this.
The difference in the ADB value (3148.06 & 3148.09) is the fact I'm trying to exclude purchase and sale amounts in the average balance. Column D almost should be called "Equity Debit/Credit Balance". Then the question arises in how would one exclude the purchase & sale amounts in the equity balance?
Thanks again for the help on this.
-Manny
This works (I think...) when the range to be calculated is exactly equal to the range of the table on the left. Is this going in the correct direction??
=SUMPRODUCT((E2:E17)*(D3:D18-D2:D17))/(MAX(D2:D18)-MIN(D2:D18))
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU
Thanks Glenn. We are going in the right direction. The =if formula to exclude type entries will work to achieve the equity balance goal. Now what I'd like to figure out is how we can use the a date range to analyze the average balance over a period of time. To add difficulty to the project, I need to be able to find the average daily balance between to dates, even if the dates are not on the table.
I am uploading your recent spreadsheet modification (update 2) for reference.
Let's say I want to know the average daily balance from 1/5/07 thru 2/28/07. Note these dates don't exist in the table. Therefore, to find the starting balance in the date range, the formula needs to find the date and balance posted in the table before the desired start date, use this value along with the desired start date which in this case would be 1/5/07. The same would need to be done to find the end date value.
want to know the average daily balance thru 2/28/07. There is no 2/28/07 in the table. The formula would find the 3,935 (last known balance on 2/3/07) is the balance to use to find the average.
Thanks Again
-Manny
Please try
=LET(z,$A$2:$C$17,st,F1,en,F2+1,y,FILTER(z,INDEX(z,,2)="Equity Debt/Credit"),d,INDEX(y,,1),s,SEQUENCE(ROWS(y)),x,MMULT(--(s>=TRANSPOSE(s)),INDEX(y,,3)),
m,IFERROR(SMALL(d,s+1),en),n,--TEXT(IF(m>en,en,m)-IF(d>=st,d,st),"0;\0"),SUMPRODUCT(x,n)/SUM(n))
Scr.png
Last edited by Bo_Ry; 12-26-2021 at 02:37 PM.
Thanks Bo_Ry. The formula is returning a #Value! error.
-Manny
There seems to be a mistake in your "check data at rows 60&61. Corrected.
I created 3 named ranges (CTRL-F3 to view Edit).
Formulae amended. see sheet. I have a very nasty feeling that I know what you're going to hit me with next.
Hahaha. When Bo comes along, I give up. I can't compete!!
Thanks Glenn. Seems to work great until I try to place a February 2007 date as a end date. If I go beyond 2/1/07, I get a #N/A in the return cell (G7).
-Manny
What do you want it to do? If you have no data beyond 3rd February... you have no data... just guesswork!!
Another guess...
Tough one. Formula still not creating the right answer when end date is set to 2/3/07. in fact, it's returning a #N/A when using 2/3/07 as the
end date which is a date that contains data. The correct answer should be 3,147.85075.
On a side note, dating the end date out past the last data entry would be a assumption that is fine in this problem. One would use the last balance calculated
from the last data entry in the table. So as an example, if I set the end dat to 12/31/2007, the formula should use 3935 as a balance for every
date after 2/3/07. For this example, if you set your start date to 11/29/06, end date to 12/31/07, result would be 3,802.512563.
I've uploaded updated spreadsheet #3.
-Manny
You have too many copies of the file open. You are getting mixed up. Close all of them.... except for the one posted at Post 19....
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks