I trade the commodities market and at the end of each month I have to manually change the formula to determine the monthly results....see attached xls, cells that are highlighted in yellow (R11, R15, R22, R30).



Is there a way to change the formula so when the month changes the formula will know when to start counting again AND keep the stating point until the month changes again?

Thanks.

Bruce

Attached Files

IGT_Forum_1.xlsx (53.9 KB, 12 views)

With current design - NO!

You are trying to put all in single table - data entry, various summaries, some rows I can't even imagine for what they are there - and then you want to apply formulas on this mess!

Create on separate a table where all info is entered. And on other sheets you create summary tables/reports, where you can determine e.g. the time period, and according summary for this period is calculated from entry table.

Some rules for entry table:
1. Into one column you enter only one type of info. In your current table you have the column Exit Price - and there are values "DATA" and "Software"! When these are some real entries, then these values must be in some other column - they aren't prices!;
2. When column contains a formula, then the same formula must be in all rows of table;
3. Calculations in table (formulas) must not depend on row position. My advice is, have a column with date and time for every row, and use this column to decide the order of rows. Then you can calculate e.g. total profit for January likeIn same way you can calculate running balance for every row - using SUMIFS() to sum all costs/purhcases/sales/etc from start of table until datetime of current row. Current row and all entries earlier than current row are summed, all entries later then current row are left out. And where this entry is positioned in table doesn't count at all.

Lots to think about.

Thanks for taking the time to point out its issues.

Bruce

P.S. this xls does what I need it to do (keeps track of my profits/losses)....just wish its would calculate the monthly profits without changing the formula each month.

So the choice is yours! Either edit formulas every month, or redesign the workbook so it keeps track of your profits/losses, AND makes all needed calculations without editing formulas every month.

It is common approach where user sees Excel as some advanced sort of ledger, where user enters some data, and Excel must do some wonders with them. Often it can't. Instead look at your application as at database, and keep data entry and output separate whenever it makes calculations simpler. You make the life easier for yourself, and for Excel too. And you can have practically unlimited number of different outputs (reports) based on same data.

I'd prefer the do it the correct way but to be honest, I believe its beyond my ability level.

So, I'll just have to modify the formula each month (not by choice).

Thanks again for your help.

Bruce

In attached workbook, I added 2 worksheets - Commodities and DataEntry. Both worksheets contain a defined Table (Commodities was needed, so I could define a dynamic commodity list for data validation, and also for avoiding defining a dynamic range for whole Specs3 table for VLOOKUP's).

At top of DataEntry sheet I left some empty rows. You can put a table like in your original sheet there, or you can enter SUBTOTAL formulas there to SUM or COUNT column entries filtered using autofilter feature. NB! When you enter something at top of page, leave the row above Table empty - otherwise Excel can at some point decide, that header row is above current one and expand to top - making a mess of all formulas.

You can use table on sheet DataEntry as source for any reports you can think off.

To add new rows into defined Tables (adding all formulas, formats, data validations, etc. automatically):
a) select right lower cell of Table, and press TAB;
b) select any cell in Table, select Design from top Menu, click Resize Table button at left of menu ribbon, and change row number in table range;
c) simply add e.g. new EntryDate into next row.

You can change column names in Tables at will - the formulas adjust automatically. But when column names contain spaces, or special characters, the formulas are difficult to read, as mostly only a part of formula will be visible on formula field - you have to scroll through formula character-wise in edit mode to pass space/special character, to see next part of formula.

I used different format for dates/times, as yours were for me difficult to read. Format date/time columns as you prefer - it doesn't affect formulas.

Attached Files

IGT_Forum_1.zip (54.1 KB, 10 views)

I appreciate all the work and I'll take a look at it later tonight.

Bruce

Obviously, your spreadsheet is must more efficient than mine and it makes complete sense to switch over.

For now I'm going to stick with my spreadsheet and make the manual formula changes (still looking a formula) until I have the time to switch over....ultimately, my goal is to switch over to your spreadsheet.

I appreciate all your help.

Bruce

P.S. A few days ago I posted the same question(s) on another xls forum and just received a formula that might work (I still need to work on it a bit).

=IF($I7>0,$P7-LOOKUP(EOMONTH(DATE(YEAR($B$7),MONTH($B7),1),-1),$B$6:$B$1000,$P$6:$P$1000),"")