Here is the updated file
Here is the updated file
Last edited by Goodvibe; 01-21-2021 at 10:26 PM. Reason: Updating file
I'm not sure I understand completely. Can you upload a file with your EXPECTED Results?
Hi Greg!
Just updated the file to what I'm looking for (I took BLUE account for example)
Let me know if it's still not clear enough! Thanks for taking the time and trying to help!
In D6, try:
=INDEX($E$14:$E$25,MATCH(C6,$C$14:$C$25,0))
And copy down.
=SUMIFS($D14:D$25,$C14:C$25,C14)
and copy down.
A bad news and a good news :P!
Bad one: Formula 1) Which is the latest balance. It doesn't give me the latest balance as soon as I add a new transaction or row with a new amount if you want (I have to adjust the cell range in the formula each time manually). I think the formula has to have a condition related to the current date maybe (I don't know if there's a condition to tell Excel to look for the closest date transaction from the current day.
Good one: The good one is that the SUMIFS "formula 2)" works ! Thanks!
I find it interesting that your list has the most recent date at the top. Is that the way it's going to be? So do you insert a row every time you want to add data? Usually the oldest date would be at the top and you would add new entries to the bottom of the list. I would also make an EXCEL Table out of the data. Then as you add transactions to the bottom of the table, the table automatically expands as do the formulas. I've attached a sample of what I mean.
Will this work for you?
Hi Greg!
No I just did it this way fast without thinking that it would've made a difference in the composition of the formula (my mistake). I have a lot to learn :P!! I'm starting step by step to understand how excel works and operates (lol not an easy task when you don't know the language "codes" ). Like you suggest, it is in a table and the dates will be from the oldest on top and the newest dates in the bottom. I wasn't sure that it would automatically updates the balance from putting the dates in that order. Thanks for the advice and the help again ! All the best on your projects Greg!
Thanks and good luck and let us know if you have any questions.
Thanks Greg! I appreciate it !
Hi Greg (and also anyone who reads the issues that I have)!
*I've add two columns (BALANCE REPORT and PROFIT/LOSS) with additional rows (semestrial report). I will enter the data of the report balance manually and I thought of just adding the amount of the BALANCE REPORT in the SUMIFS formula (in the SUM RANGE) but it gives me a VALUE error?
*I'm also trying to find the formula to calculate the profit or loss for each account?
Thanks in advance!
Last edited by Goodvibe; 01-20-2021 at 01:01 PM. Reason: Add another question
Sorry I don't understand. I don't see any formula giving a #VALUE error in the file you attached. In what cell(s) are you looking for help? Is it column E (specifically in E22:E25?) What's the logic of how you come up with the numbers?
Hi Greg!
Sorry, I maybe did a mistake sending an older file. It should be good now (I've marked the formulas that I was looking for in comments in differents cells (yellow post it). If you activate the modification mode, you should see the comments open. I've put the tabs involved in black to let you know which tabs that I look for formulas. Let me know if I ask too much by the way and like always, thanks for helping out! Still doing my research on my side but not much luck so far lol :P.
Did you upload a file? I tried the one from your previous post but it looks identical to the one that was there before. I don't see any yellow post it.
I'll need to know
1) what cell(s) you're looking for help with,
2) what the expected result is
3) and why (what's the logic for the result)
I just opened it again and everything is mentioned (I should've told you that it's in the original post sorry). I don't know how to attach a file in the reply section so that's why I updated it in the original post :S. All 3 questions that you just asked are in the file (would take too long to detail in here).
This would be a bit simpler if you were using Version MS365, but this should work for your version. In cell F22, try this:
=[@[BALANCE
REPORT]]-INDEX($G$14:$G$21,SMALL(IF($C$14:$C$21=C22,ROW($C$14:$C$21)-ROW(INDEX($C$14:$C$21,1,1))+1),COUNTIFS($C$14:$C$21,C22)))
You will probably need to enter this as an array formula. So instead of pressing "SHIFT", you'll need to press "SHIFT", "CTRL" and "ENTER" at the same time.
Lol Still trying to understand the logic of the formula but nope...not there yet lol.Is it possible that I can't scroll down the formula by just scrolling the cross sign (clicking on the right corner of the cell with the formula) because of the array formula?
Unfortunately, F23 F24 and F25 don't give me the right result :S. Thanks for trying though!
Last edited by Goodvibe; 01-21-2021 at 10:23 PM.
What you had in your formula bar is NOT what I had above. I put the formula in and it seems to work. I've attached the spreadsheet and the answers for F22:F25 are 15, 65, -5, and -3.
Ah ok every cell range for each functions had to be absolute. Though I still don't get the formula lol but it works :P. Thanks a lot!
Still trying to find a formula for the other tab (Projection) on D2?
Besides practice, is it possible or is there a trick/advice you can give to figure out how to compose those types of formulas when there is multiple functions interacting with each other?
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks