Forum Discussion
I need a formula to SUM the Bank and ROI columns by day / week, etc. for single and Multi Bet 2,3,4
I am very weak in excel, and stuck at the last stage in the table, so I am looking for help on the Internet. I have created a "Bank + ROI" Sheet for keeping statistics. Everything is simple here: there is a summation of money by days / weeks / months / years / all time and the ROI percentage of each bet also by days / weeks / months / years / all time. When I select single bets on the "Bets" Sheet, then my formula sums everything up correctly, but when I select "M" (Multi Bet 2,3,4+) express bet in the "Bets" Sheet and the rates appear from 2-4 + ( depending on the selected type of bet), then empty cells appear in the Sheet "Bets" -Date, and my formula no longer fits. Is it possible to find a beautiful formula to solve my problem, so that the sum goes further down the column, despite the empty cells in the Date, or do I do something for this. While my knowledge is not enough to find the answer, perhaps there is a guru of this program here. ----------------------------------------> https://dropmefiles.com/Nn3ra
THX for any help
BetsBank+ROI
noporschenolife Here is a formula for the day by day running total and you should be able to modify for the other columns:
=IF(Bets!A27="", "",IF(Bets!A27=MAX(Bets!A$11:A26),Bets!AC27+INDEX(Bets!AC$11:AC26,MAX(ROW(Bets!A$11:A26)*ISNUMBER(Bets!A$11:A26)-ROW(A$10))),Bets!AC27))Basically it checks if this date is = to the max of all previous dates (i.e. doesn't care if there are spaces) then if so it INDEXes the column of data based on the last row that ISNUMBER.
BTW if you are using Excel 365, you might be able to make an easier formula using FILTER and LET functions.
6 Replies
noporschenolife Here is a formula for the day by day running total and you should be able to modify for the other columns:
=IF(Bets!A27="", "",IF(Bets!A27=MAX(Bets!A$11:A26),Bets!AC27+INDEX(Bets!AC$11:AC26,MAX(ROW(Bets!A$11:A26)*ISNUMBER(Bets!A$11:A26)-ROW(A$10))),Bets!AC27))Basically it checks if this date is = to the max of all previous dates (i.e. doesn't care if there are spaces) then if so it INDEXes the column of data based on the last row that ISNUMBER.
BTW if you are using Excel 365, you might be able to make an easier formula using FILTER and LET functions.
Now I will try to use your formula and see how it works. And my excel says that this "," character in the formula is wrong and I have to use this ";" character. Anyway, I can't believe someone answered me on the internet and helped, I'm very glad. The fact that you described how this formula works sounds simple, but I myself would not have thought of this myself, since I am simply not yet familiar with these formulas (MAX, INDEX and ISNUMBER, as well as what you wrote about filters) ... You are smart. If the formula works, how can I thank you?
- You're very welcome and if you have any questions about how they work or problems getting them to work just let me know. Your appreciation and thanks is all we ask for 🙂
