Urgent Need to Optimize a slow Cashier Balance spreadsheet (with Price Control)

[Lorange]

Hi,


We are a small hair/beauty salon in Brasil. With a lot of researches and useful help (thanks to Peter_SSs and vogel997 from this forum), I have created a Monthly Sales spreadsheet to record the sales, receive payments and give appropriate change (for cash payments). All services are listed in another spreadsheet and have their price defined for different periods of time. This is because prices may change from time to time, and we should be able to retrieve the right price based on a given date. I'm working with Google Spreadsheets for different reasons, but it's mostly the same as Excel.


The monthly sales spreadsheet looks like this:

Here are samples of these two spreadsheets (I translated a few parts in English to make it easier to understand):

• https://docs.google.com/spreadsheets/d/1yr6fiAnBWSedbn4trKEXR6lgm2leeQiXLQ1WxsKeThc/edit?usp=sharing (Monthly Sales)
• https://docs.google.com/spreadsheets/d/1apKOpEY_MUl1NCAI06utYCeTm2Qb66dqTWiNNH6xPDc/edit?usp=sharing (Price control)

The spreadsheet works quite well and fast on my home computer. But it is very slow on the computer of the hair salon, which is only 1 year old (1 CPU Celeron J1800, 4GB RAM, very clean setup). When entering a code, it takes approx. 3-5 seconds to get the service name and price. It might sound short, but this really makes the spreadsheet unusable and very disturbing for the cashier, especially when there are a lot of transactions to enter.


I really hope there is someone who could help me optimize it. The critical and most important function is the one you can find in the hidden columns H (replicated in colmns O, V, ...). This is also the only function that I was not able to put into an ArrayFunction(). But I don't know it it's the only weak point of the spreadsheet.


I'm sure this is a great spreadsheet and that others may find a use for this. This is why I put a public copy of it here.


I really hope we can get it better. Thank you for any help!


Laurent

 

[Dryver14]

I notice that most of your dates of validity seem to be non changing maybe just highlight the ones that do change on the price sheet.

you could then just look at these ones periodically to change them.

If that was the case in H6 of the front page you could just put =IF(D6="","",VLOOKUP(D6,'#Prescos'!A2:C2000,3,0))

I do not understand the first formula running that slow though

 

[Lorange]

Hi Dryver and thank you for taking the time to look at my problem. But what do you mean with "highlight the ones that do change on the price sheet."?

 

[mole999]

is the file accessed from the web, and do you have the same network access speeds at both locations

 

[Dryver14]

On your price sheet I see about 6 things that seem to change price for "seasonal reasons", what I mean is literally go onto the price page and change the colour of those prices,
that way when you take a peek occasionally you know these are the specific prices you need to change. that way your formulas to not need to look through every start and finish date as part of the equation. If say, 100 prices changes regularly then yes dates would be a worthwhile addition. you could even have just an end date for the item and a simple formula somewhere on the front page that if that date is exceeded you need to look at it

 

[Lorange]

is the file accessed from the web, and do you have the same network access speeds at both locations

I'll be right now taking the computer of the hair salon and bring it bak here at home to make some tests. Because the network access in the hair salon is sometimes overloaded (ip cameras, shared wi-fi for customers, ...).

 

[oldbrewer]

102

< < < CODE

mytable

customer

Mrs Jones

date

manicure

pedicure

waxing

01/07/2015

15

13

22

today's date

03/01/2016

01/01/2016

17

15.5

23

01/07/2016

18

17

24

product

pedicure

price

15.5

mytable2

manicure

3

pedicure

2

waxing

1

formula giving 15.5

=VLOOKUP(H7,mytable,(5-VLOOKUP(H9,mytable2,2)))

mytable3

101

manicure

102

pedicure

formula giving pedicure

103

waxing

=VLOOKUP($C$1,mytable3,2)

formula giving 03/01/16

=TODAY()

<colgroup><col span="6"><col><col span="5"><col><col><col span="3"></colgroup><tbody>
</tbody>

 

[Lorange]

On your price sheet I see about 6 things that seem to change price for "seasonal reasons", what I mean is literally go onto the price page and change the colour of those prices,
that way when you take a peek occasionally you know these are the specific prices you need to change. that way your formulas to not need to look through every start and finish date as part of the equation. If say, 100 prices changes regularly then yes dates would be a worthwhile addition. you could even have just an end date for the item and a simple formula somewhere on the front page that if that date is exceeded you need to look at it

This is because 2015 was our first year of activity... but in a few days, most prices will increase (new 2016 prices), and this will probably the same in the next years. Every year, because of high inflation rate in Brazil, most prices will be "updated".

 

[Dryver14]

Mole999 I see where you are going with your question you are asking if these prices are set locally. That never crossed my mind... a question for future answers.

 

[Lorange]

Actually everything is online (in the "cloud"), because I'm using Google spreadsheets.

You can even open these two spreadsheets, make a copy (in your Google account) and play with them.

• https://docs.google.com/spreadsheets...it?usp=sharing (Monthly Sales)
• https://docs.google.com/spreadsheets...it?usp=sharing (Price control)