Conceptually challenged…

6.66K viewsFormulas and Functions

How do I include income and expense items that are credited/debited on different cycles? An example file is attached.

Weekly Settlements are completed each Friday.
Weekly Settlements are paid via direct deposit each Monday.

Weekly Settlements include:

Payment for Work Orders for which associated documents have been recieved by mail by the previous Tuesday (basically anything mailed by the Friday before that Tuesday). A Work Order may or may not span 2 seperate “Fuel Weeks” (see below). Work Orders take from several hours to several days to complete.

Expenses deducted include:
Fuel bought with Fuel Card during the previous “Fuel Week”, which ends on Saturday (regardless of associated Work Order #), and is deducted from the next Weekly Settlement (which is completed the following Friday).

I would like to enter individual Fuel purchases into a “Fuel Purchases” matrix, and have the “Fuel” item under each “Pay Wk” column in the “Weekly Expenses” matrix automatically show ONLY the total cost of all Fuel that was purchased between Sun through Sat of the previous “Fuel Week”.

Anyone have any ideas? Am I going about this the wrong way?


That’s great!
It is kind of fun to build these models since your not having to consume your brain resources trying to retain what B37 represents.

I always enjoy looking at the statistics and calculating the ratio of formulas to calculated cells (i.e. how many spreadsheet formulas it would take). Of course, it is very dependent on the model requirements but I like to see how high I can get it. Your current model is 44 to 1. It’s kind of fun.

You are viewing 1 out of 11 answers, click here to view all answers.

Latest Questions