Ask Your Question
0

Calc: Running total based on Weeks

asked 2018-05-12 10:08:28 +0200

Sights gravatar image

updated 2018-05-13 00:47:58 +0200

LibreTraining gravatar image

I'm developing a spreadsheet that holds a list of customers that are given a weekly order at a local market. Sheet A is the customer list with their order in columns C - F, which is then totalled in column J. Column K is a flag for orders placed on hold while a customer is unable to collect an order while away.

image description

The order quantities rarely change so I'm wanting to total the same numbers in the same columns each week.

Sheet B shows the same customers with the accumulating total in column B, and, even though it's not shown in the example, column C will freeze the total of any order that is flagged in sheet A, column K, with a '"h".

image description

So, I think I need to look at the date to see when a week rolls over, so the order can be recalculated to update the running total, but haven't worked that out yet. I need to find a way of counting the seven days and calculating the (accumulating) running total. I'm not very experienced at spreadsheets, so would appreciate someone out there showing me the light.

If anyone has the time, I would also like to know how to set monthly, quarterly and yearly totals.

Many thanks in advance.

edit retag flag offensive close merge delete

Comments

Quoting @Sights: "Sheet A is the customer list with their order in columns C - F, ..."
That's not what I see - and it's even nothing I would assume possible.
If not a customer is restricted to order once and never again, you need to list the orders using either a "foreign key" into a customer list or the customer name itself in this role.
Concerning the weekly totals you will run into vast complications as long as not the date is made part of each order (row of the respective sheet).

Lupp gravatar imageLupp ( 2018-05-12 11:50:49 +0200 )edit

Please don't append images, but real examples (.ods file in case of Calc questions).

Lupp gravatar imageLupp ( 2018-05-12 11:52:27 +0200 )edit

Contributors from the world of real business would advise you to not use Calc for the purpose behind your question, but to create a database for it.

Lupp gravatar imageLupp ( 2018-05-12 11:54:10 +0200 )edit

The example is not clear. How does $9.00 and 3 total $13.50? Or $6.00 and 7 total $6.00? Please edit the question to give a simplified example that demonstrates what you are asking. See guidelines for asking.

Jim K gravatar imageJim K ( 2018-05-13 08:02:30 +0200 )edit

2 Answers

Sort by » oldest newest most voted
0

answered 2018-05-13 14:06:39 +0200

Lupp gravatar image

Wrongly assuming you might even be less familiar with databases (which I had to "respect" for some time, but didn't like myself), I made an example for the running subtotal regarding the hints I gave in my comment above.

The result does neither emphasize "prettysheeting" nor strict closeness to the structure shown in the images attached to the question. My concern was to exemplfy an idea how to do such things with spreadsheets without disregarding the most basic principles of databasing.

As I very rarely designed a (even extremely simple) database myself, I may not be the best advisor in this case. Since the mentioned example once was made, however, I now attach it here with due modesty.

edit flag offensive delete link more

Comments

Thanks very much for your effort Lupp, I do appreciate the time you put into this example. I will have a better look at it tonight, and also the possibility of creating a database. Used to to them 25 or more years ago, when I worked in IT, but probably a little rusty by now - should be fun.

Sights gravatar imageSights ( 2018-05-14 07:16:25 +0200 )edit
0

answered 2018-05-13 13:34:25 +0200

Sights gravatar image

Sorry Lupp, I did say I was a novice! : (

This is the first time I have posted an example of my sheets. I didn't know I could post "real examples".

Maybe I should use a database, I am much more proficient at programming in that area.

Hey Jim K, the figures in columns C,D & F, represent numbers of Avocardos. There is a formula multiplying the unit price for 1st and 2nd grade fruit, while the 3rd grades in column F are free - hence $6 and 7 total $6.

Sorry for the confusion, and maybe I will look at starting again with a database. : )

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2018-05-12 10:08:28 +0200

Seen: 70 times

Last updated: May 13 '18