Conceptually challenged…

6.41K 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?


I am glad you like the changes. Your idea about using a data entry matrix that mimics the workflow is a standard practice (don’t forget about a possible datalink) and then let Quantrix move it around as necessary.

The time you spend learning to think alittle differently (I call it thinking from the outside in- working with the catagories not the data) will have a huge payoff. I think your model is a great project to cut your teeth on. [Hint: consider doing a projected cashflow once you have the base model working]

As for sumif, this is the command: sumif(range, condition, [value_range]). It reads “sum if in [u:1rfuudmk]this range of values [/u:1rfuudmk][u:1rfuudmk]this condition is met[/u:1rfuudmk] with the [u:1rfuudmk]values from this range[/u:1rfuudmk]”. It can be found in the StatisticalFunctions.model.

See if that works for you.


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

Latest Questions