Cashier Spreadsheet Project: Problem #1 - Simplifying a Formula

[ackalbo]

Hello everyone

I have recently acquired a small bar and decided that the Cashier Spreadsheet needed a rather major overhaul (business is too small to justify a POS system). I'm finding that the more time I spend on this, the more I think about the information that I would like to extract and how to go about this, therefore this has become somewhat of a project for me now which I find very interesting (from a delving deep into Excel perspective).

A little background info - The business hours are 3pm-2am. Drinks are cheaper 3pm-6pm (Happy Hour or "HH") after which, drinks revert to regular pricing until closing. Drinks are allowed to be purchased for staff members at an increased price. So, the cost of the drink depends on three factors:

• Time of Purchase
• Recipient of Purchase
• Type of Drink Purchased

Visually, the spreadsheet looks like this:

Excel 2010

	A	B	C	D	E	F	G
2	Time	Order No	Category	Item	Unit Price	Qty	Total Unit Cost
3
4	16:15	0001	Customer Drink	SML	75	1	75
5	16:15	0001	LD	SML DLD	250	1	250
6	20:32	0002	Customer Drink	SML	95	1	95
7	20:32	0002	LD	SML DLD	300	1	300

<tbody>
</tbody>

Data Entry

Field Explanation:

Column A - Formatted as 24hr and manually entered.
Column B - Receipt No and manually entered.
Column C - Cell drop down list (Data list on separate worksheet 'Background Data')
Column D - Cell drop down list (Data list on separate worksheet 'Background Data')
Column E - Contains the formula in question (Posted below). This formula determines the time, Category and type of drink in order to determine the Unit Price.
Column F - Manually entered.
Column G - Simple formula to calculate the Total Unit Cost.

Below is the spreadsheet with the formulas shown:

Excel 2010

	A	B	C	D	E	F	G

<tbody>
[TD="align: center"]2[/TD]
[TD="align: center"]Time[/TD]
[TD="align: center"]Order No[/TD]
[TD="align: center"]Category[/TD]
[TD="align: center"]Item[/TD]
[TD="align: center"]Unit Price[/TD]
[TD="align: center"]Qty[/TD]
[TD="align: center"]Total Unit Cost[/TD]

[TD="align: center"]3[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]4[/TD]
[TD="align: center"]16:15[/TD]
[TD="align: center"]0001[/TD]
[TD="align: center"]Customer Drink[/TD]
[TD="align: center"]SML[/TD]
[TD="align: center"]75[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]75[/TD]

</tbody>

Data Entry​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]E4[/TH]
[TD="align: left"]=IF(D4="","",IF(AND(A4>0.583333,A4<0.75,'Data Entry'!C4="Customer Drink"),VLOOKUP(D4,'Price List'!$A$3:$E$123,3,FALSE),IF(AND(A4>=0.75,A4<0.999999,C4="Customer Drink"),VLOOKUP(D4,'Price List'!$A$3:$E$123,2,FALSE),IF(AND(A4>=0,A4<0.25,C4="Customer Drink"),VLOOKUP(D4,'Price List'!$A$3:$E$123,2,FALSE),IF(AND(A4>0.583333,A4<0.75,'Data Entry'!C4="LD"),VLOOKUP(D4,'Price List'!$A$3:$E$123,5,FALSE),IF(AND(A4>=0.75,A4<0.999999,C4="LD"),VLOOKUP(D4,'Price List'!$A$3:$E$123,4,FALSE),IF(AND(A4>=0,A4<0.25,C4="LD"),VLOOKUP(D4,'Price List'!$A$3:$E$123,4,FALSE),IF(C4="Party Animal",VLOOKUP(C4,'Price List'!$A$3:$E$123,2,FALSE),IF(C4="RTB",VLOOKUP(C4,'Price List'!$A$3:$E$123,2,FALSE),IF(C4="Tossed Balls",VLOOKUP(C4,'Price List'!$A$3:$E$123,2,FALSE),IF(C4="Shirt",VLOOKUP(C4,'Price List'!$A$3:$E$123,2,FALSE),IF(C4="Beer Wrap",VLOOKUP(C4,'Price List'!$A$3:$E$123,2,FALSE),IF(C4="Towel",VLOOKUP(C4,'Price List'!$A$3:$E$123,2,FALSE),IF(C4="Key Chain",VLOOKUP(C4,'Price List'!$A$3:$E$123,2,FALSE),""))))))))))))))[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]G4[/TH]
[TD="align: left"]=IF(E4="", "", F4*E4)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



A sample of the 'Price List' worksheet which is referenced within the VLOOKUP functions:

​Excel 2010

	A	B	C	D	E
Other - Not Listed
Absolut Vodka
Absolut Citron
Absolut Mandarin
Absolut Raspberri
Amaretto
Antonov Ice

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="align: center"]Cust Reg[/TD]
[TD="align: center"]Cust HH[/TD]
[TD="align: center"]LD Reg[/TD]
[TD="align: center"]LD HH[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]

[TD="align: right"] 160 [/TD]
[TD="align: right"] 135 [/TD]
[TD="align: right"] 215 [/TD]
[TD="align: right"] 195 [/TD]

[TD="align: center"]5[/TD]

[TD="align: right"] 160 [/TD]
[TD="align: right"] 135 [/TD]
[TD="align: right"] 215 [/TD]
[TD="align: right"] 195 [/TD]

[TD="align: center"]6[/TD]

[TD="align: right"] 160 [/TD]
[TD="align: right"] 135 [/TD]
[TD="align: right"] 215 [/TD]
[TD="align: right"] 195 [/TD]

[TD="align: center"]7[/TD]

[TD="align: right"] 160 [/TD]
[TD="align: right"] 135 [/TD]
[TD="align: right"] 215 [/TD]
[TD="align: right"] 195 [/TD]

[TD="align: center"]8[/TD]

[TD="align: right"] 95 [/TD]
[TD="align: right"] 75 [/TD]
[TD="align: right"] 150 [/TD]
[TD="align: right"] 135 [/TD]

[TD="align: center"]9[/TD]

[TD="align: right"] 95 [/TD]
[TD="align: right"] 75 [/TD]
[TD="align: right"] 150 [/TD]
[TD="align: right"] 135 [/TD]

</tbody>

Price List​

One will notice that there are other items referenced in the formula within 'Data Entry'C4 such as "Party Animal" and "Shirt", these are items that are sold for a singular price regardless of whether they are for a customer or staff - These non dependant items are causing their own problem i.e. If "Shirt" is selected from the drop down list in cell C4 for some reason the price will not be looked up unless something from the drop down list in cell D4 is selected (which should not be required).

So, guys and gals, any suggestions on simplifying the well nested IF formula in cell E4?

Any assistance will be greatly appreciated.

 

[Jerry Sullivan]

Hi ackalbo,

I'd suggest you try to organize your data so that you can use a relatively simple INDEX-MATCH formula to lookup the price on your Price List.

Ultimately it would take the form of...
=INDEX(PriceData,MATCH(D3,INDEX(PriceData,0,1),0),ColumnIndex)

Where:
PriceData is your Price List Lookup Range
MATCH(D3,INDEX(PriceData,0,1),0) evaluates to the Row that the item found in the Price List
ColumnIndex is the Column to be read on the Price List

The tricky part is translating the 3 factors into a ColumnIndex.
It would simplify things if you added another column "Fixed Price" to the price table for items like "Party Animal" and "Shirt"; and added "Fixed Price" to the drop down lists in Category.

This would allow us to derive ColumnIndex based on just the two factors Time and Category.

If that sounds like an approach you would like to pursue, I'll suggest some options.
One thing that isn't clear to me is what price is used when the time doesn't fall into one of the two ranges in the existing formula
A4>0.583333,A4<0.75
A4>=0.75,A4<0.999999
I presume there isn't a third rate based on time < =0.583333.
If you'll confirm that, then the formula will just need to consider that anything outside of the Happy Hour range is Regular rate.

EDIT: I just noticed the part of the existing formula addresses Midnight to 2:00 AM. I'd still suggest simplifying that to Happy Hour and everything else.

 

[ackalbo]

Hi JS411. Thank you for the reply and asstance

I haven't used the INDEX - MATCH formulas before although I've come across them in the past. I shall research them now, see how to use them and get back to you here.

EDIT: I just noticed the part of the existing formula addresses Midnight to 2:00 AM. I'd still suggest simplifying that to Happy Hour and everything else.

I agree, originally the formula was set up to determine whether or not a sale was between 2pm - 6pm and 6pm - 3am but with the 2nd time frame I encountered problems with crossing over midnight.

Be back soon, off to Google INDEX & MATCH....

 

[ackalbo]

It would simplify things if you added another column "Fixed Price" to the price table for items like "Party Animal" and "Shirt"; and added "Fixed Price" to the drop down lists in Category.

I was just wondering, would it make things easier or harder if "Merchandise" was added to the Category drop down list and then the separate merchandise items listed at the end of the Items drop down list?

At present the "Fixed Price" merchandise items are listed at the bottom of the Items PriceList but actually appear within the Category drop down list:

Excel 2010

	A	B	C	D	E
Tia Maria
Tomato Juice
VO
Vodka (Local)
Wild Turkey
Wine, Red
Wine, White
Party Animal
RTB
Tossed Balls
Shirt
Beer Wrap
Towel
Key Chain

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="align: center"]Cust Reg[/TD]
[TD="align: center"]Cust HH[/TD]
[TD="align: center"]LD Reg[/TD]
[TD="align: center"]LD HH[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]110[/TD]

[TD="align: right"]160[/TD]
[TD="align: right"] 135 [/TD]
[TD="align: right"] 215 [/TD]
[TD="align: right"] 195 [/TD]

[TD="align: center"]111[/TD]

[TD="align: right"]85[/TD]
[TD="align: right"] 65 [/TD]
[TD="align: right"] 140 [/TD]
[TD="align: right"] 125 [/TD]

[TD="align: center"]112[/TD]

[TD="align: right"]160[/TD]
[TD="align: right"] 135 [/TD]
[TD="align: right"] 215 [/TD]
[TD="align: right"] 195 [/TD]

[TD="align: center"]113[/TD]

[TD="align: right"]95[/TD]
[TD="align: right"] 75 [/TD]
[TD="align: right"] 150 [/TD]
[TD="align: right"] 135 [/TD]

[TD="align: center"]114[/TD]

[TD="align: right"]160[/TD]
[TD="align: right"] 135 [/TD]
[TD="align: right"] 215 [/TD]
[TD="align: right"] 195 [/TD]

[TD="align: center"]115[/TD]

[TD="align: right"]105[/TD]
[TD="align: right"] 95 [/TD]
[TD="align: right"] 160 [/TD]
[TD="align: right"] 155 [/TD]

[TD="align: center"]116[/TD]

[TD="align: right"]105[/TD]
[TD="align: right"] 95 [/TD]
[TD="align: right"] 160 [/TD]
[TD="align: right"] 155 [/TD]

[TD="align: center"]117[/TD]

[TD="align: right"]2000[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]118[/TD]

[TD="align: right"]3500[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]119[/TD]

[TD="align: right"]400[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]120[/TD]

[TD="align: right"]350[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]121[/TD]

[TD="align: right"]150[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]122[/TD]

[TD="align: right"]150[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]123[/TD]

[TD="align: right"]100[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Price List​

 

[ackalbo]

Hi JS411

After playing around with INDEX - MATCH for awhile I now understand (I hope) that:

MATCH - Provides the position of an element within an array.
INDEX - Provides an element based on position.

Providing my understanding of this is correct then I have been "stripping down" the formula that you have kindly suggested and there is a part that I find confusing.

The formula is as follows:

=INDEX(PriceData,MATCH(D3,INDEX(PriceData,0,1),0),ColumnIndex)

which I have adapted (as a test) to:

=INDEX('Price List'!A4:F116,MATCH(D20,INDEX('Price List'!A4:F116,0,1),0),3)

The bold type is the area causing me some confusion, if I am reading this right then the formula is looking at Row 0, Column 1 within the range 'Price List'!A4:F116 but there isn't a Row 0 and when the formula is used on it's own "=INDEX('Price List'!A4:F116,0,1)" then #VALUE is the result yet the entire formula as a whole, works yet it my mind the MATCH function ends up being MATCH(D20,#VALUE,0)........

What (if anything) am I missing here?

 

[Jerry Sullivan]

Looks like you're making great progress!

Using INDEX with a 0 value for the Rows argument returns all Rows.
INDEX('Price List'!A4:F116,0,1) should return a reference to range 'Price List'!A4:A116

The same result could be accomplished for your current layout with
=INDEX('Price List'!A:F,MATCH(D20,'Price List'!A:A,0),3)
...however by using a named range and Indexing the first column, it allows you to change the location of your lookup table without needing to modify all the formulas.

 

[ackalbo]

A big thank you to JS411 for pointing me in the right direction with this

Finally finished.......except for one tiny little problem......I foresee a "Cashier Spreadsheet Project: Problem #2" coming up...

 

[Jerry Sullivan]

Glad to hear that you worked through a solution. You're welcome to add that Problem #2 to this thread, or start another.