Cash register spreadsheet

[tsameti]

First of all apologize if this is not the right place to ask for this sort of help.
Can anyone help creating this simple spreadsheet.
I have created one myself but I can make it work just the way I want it.
In the end I had to simplify it to the point where I enter almost all data except for calculation and even that stops if the item number is higher than 2.


=IF(B35=1,B35*27.5,IF(B35=2,B35*27.5))
This is as far as I went and I cant make graphics work either, as for now I simply change color on Item QTY manualy to know what was sold.

 

[oldbrewer]

date	sale #	item	how many	sale price	cost price	profit	running sale price	running profit	running cost	%profit on sales	running %profit on sales			eraser	0.49	0.2
01/07/2016	1	pen	2	4.5	2.2	2.3	4.5	2.3	2.2	51.1	51.1			ink	1.25	0.65
01/07/2016	2	pencil	1	0.35	0.1	0.25	4.75	2.55	2.3	71.4	52.6			marker	0.99	0.4
01/07/2016	3	ruler	2	1.5	0.6	0.9	5.65	3.45	2.9	60.0	54.3			pen	2.25	1.1
02/07/2016	4	scissors	2	6.98	3.6	3.38	9.03	6.83	6.5	48.4	51.2			pencil	0.35	0.1
02/07/2016	5	stapler	1	2.49	1.2	1.29	10.32	8.12	7.7	51.8	51.3			ruler	0.75	0.3
02/07/2016	6	string	3	2.97	1.05	1.92	12.24	10.04	8.75	64.6	53.4			scissors	3.49	1.8
03/07/2016	7	tape	2	2.98	1.5	1.48	13.72	11.52	10.25	49.7	52.9			stapler	2.49	1.2
03/07/2016	8	eraser	3	1.47	0.6	0.87	14.59	12.39	10.85	59.2	53.3			string	0.99	0.35
03/07/2016	9	ink	3	3.75	1.95	1.8	16.39	14.19	12.8	48.0	52.6			tape	1.49	0.75
04/07/2016	10	marker	3	2.97	1.2	1.77	18.16	15.96	14	59.6	53.3
04/07/2016	11	pen	3	6.75	3.3	3.45	21.61	19.41	17.3	51.1	52.9
04/07/2016	12	pencil	2	0.7	0.2	0.5	22.11	19.91	17.5	71.4	53.2
05/07/2016	13	ruler	2	1.5	0.6	0.9	23.01	20.81	18.1	60.0	53.5			SALES
05/07/2016	14	scissors	2	6.98	3.6	3.38	26.39	24.19	21.7	48.4	52.7			01/07/2016	08/07/2016
05/07/2016	15	stapler	1	2.49	1.2	1.29	27.68	25.48	22.9	51.8	52.7			07/07/2016	14/07/2016
06/07/2016	16	string	2	1.98	0.7	1.28	28.96	26.76	23.6	64.6	53.1		eraser	5	4
06/07/2016	17	tape	1	1.49	0.75	0.74	29.7	27.5	24.35	49.7	53.0		ink	5	3
06/07/2016	18	eraser	2	0.98	0.4	0.58	30.28	28.08	24.75	59.2	53.2		marker	6	3
07/07/2016	19	ink	2	2.5	1.3	1.2	31.48	29.28	26.05	48.0	52.9		pen	6	4
07/07/2016	20	marker	3	2.97	1.2	1.77	33.25	31.05	27.25	59.6	53.3		pencil	3	5
07/07/2016	21	pen	1	2.25	1.1	1.15	34.4	32.2	28.35	51.1	53.2		ruler	4	5
08/07/2016	22	pencil	3	1.05	0.3	0.75	35.15	32.95	28.65	71.4	53.5		scissors	4	5
08/07/2016	23	ruler	2	1.5	0.6	0.9	36.05	33.85	29.25	60.0	53.6		stapler	2	2
08/07/2016	24	scissors	2	6.98	3.6	3.38	39.43	37.23	32.85	48.4	53.1		string	5	3
09/07/2016	25	stapler	1	2.49	1.2	1.29	40.72	38.52	34.05	51.8	53.1		tape	3	4
09/07/2016	26	string	1	0.99	0.35	0.64	41.36	39.16	34.4	64.6	53.2
09/07/2016	27	tape	2	2.98	1.5	1.48	42.84	40.64	35.9	49.7	53.1
10/07/2016	28	eraser	1	0.49	0.2	0.29	43.13	40.93	36.1	59.2	53.1
10/07/2016	29	ink	2	2.5	1.3	1.2	44.33	42.13	37.4	48.0	53.0
10/07/2016	30	marker	1	0.99	0.4	0.59	44.92	42.72	37.8	59.6	53.1
11/07/2016	31	pen	1	2.25	1.1	1.15	46.07	43.87	38.9	51.1	53.0		THIS ANALYSIS TABLE CAN BE AS DETAILED AS YOU LIKE
11/07/2016	32	pencil	1	0.35	0.1	0.25	46.32	44.12	39	71.4	53.1
11/07/2016	33	ruler	3	2.25	0.9	1.35	47.67	45.47	39.9	60.0	53.3
12/07/2016	34	scissors	3	10.47	5.4	5.07	52.74	50.54	45.3	48.4	52.7
12/07/2016	35	stapler	1	2.49	1.2	1.29	54.03	51.83	46.5	51.8	52.7
12/07/2016	36	string	2	1.98	0.7	1.28	55.31	53.11	47.2	64.6	52.9
13/07/2016	37	tape	2	2.98	1.5	1.48	56.79	54.59	48.7	49.7	52.9
13/07/2016	38	eraser	3	1.47	0.6	0.87	57.66	55.46	49.3	59.2	52.9
13/07/2016	39	ink	1	1.25	0.65	0.6	58.26	56.06	49.95	48.0	52.9
14/07/2016	40	marker	2	1.98	0.8	1.18	59.44	57.24	50.75	59.6	53.0
14/07/2016	41	pen	3	6.75	3.3	3.45	62.89	60.69	54.05	51.1	52.9
14/07/2016	42	pencil	1	0.35	0.1	0.25	63.14	60.94	54.15	71.4	52.9

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

 

[tsameti]

Looks great. Thank you.

Sales are very small, up to 30 items a day it is treated as hobby for now as I have full time job.

How do I incorporate formulas? I copied it all in the spreadsheet.

 

[oldbrewer]

your lookup table lists everything you sell, sith sale price and cost price

so when you type "pencil" into A1 in B1 the formula is =vlookup(A1, mytable,2)
in C1 it is the same with 3 at the end instead of 2

profit is obviously =B1-C1

the analysis table uses sumproduct together with match to analyse by time period and sale item

I suggest you put it together and come back if you get stuck....

MODERATORS - is it permissible to send the OP a working spreadsheet, please ?

 

[tsameti]

I have date in A
sale # in B
Item = C
How many = D
Sale price = E
Cost Price= F
Profit = G
Running Sale price = H



Does this formula need to be in E2? =vlookup(A1, mytable,2)
If yes, I'm guessing A1 will be C2
but I dont understand what is mytable and number 2 and how do I create it

 

[oldbrewer]

your E and F (sale price and cost price) are obtained from the lookup.

so e2 =Vlookup(c2,mytable,2)
ans F2 = vlookup (c2, mytable,3)

mytable is just a list of ALL your products say in col M, with sale prices and cost prices in cols N and O
highlight M1 to O100 and insert, name, define as mytable, ok

the 2 is which column to bring the answer from ie col N
use 3 = col O for the cost price

 

[tsameti]

your E and F (sale price and cost price) are obtained from the lookup.

so e2 =Vlookup(c2,mytable,2)
ans F2 = vlookup (c2, mytable,3)

mytable is just a list of ALL your products say in col M, with sale prices and cost prices in cols N and O
highlight M1 to O100 and insert, name, define as mytable, ok

the 2 is which column to bring the answer from ie col N
use 3 = col O for the cost price

Ok, what am I doing wrong? I've been trying to solve the problem. I think I'm not selecting the right table or something.

 

[oldbrewer]

do the lookup table, then put in just one sale of a stapler
do you get the sale price and cost of a stapler ?

you dont select a lookup table - you just lookup a value against the lookup table

 

[tsameti]

I give up oldbrewer. )

Excel is not my thing, perhaps I should stick to manual data entry. I just can't make it work.


Thanks for your trouble anyway.

 

[My Aswer Is This]

I suggest you start with more simpler things to begin with. You said in Post #1 you had never created formulas or Vba scripting. I would start out doing small things and learn more from this forum. We all have to learn to ride a tricycle then a bike and then a car and then a Sports car. What your asking for here is like trying to drive a Sports car. This forum can help you but were not able to do it all for you. Take care and do not give up.

I give up oldbrewer. )

Excel is not my thing, perhaps I should stick to manual data entry. I just can't make it work.


Thanks for your trouble anyway.

 

[oldbrewer]

in G1 to G3 put green, red yellow
in H1 to H3 put 75,34,22
highlight G1:H3
insert,name,mytable
in A1 put red
in a2 put =vlookup(a1,mytable 2)
you should see 34

report success and I will guide you through more steps to develop what you want

MODERATORS - can I send the OP a kind of blueprint spreadsheet please ?