Challenge - Excel for Internet Cafe

[pravin_av]

Hello
I own a internet cafe with 14 cabins.
I created a simple sheet like this with columns
A | B | C | D | E
1 |cabin no.|time-in |time-out|net-time|rate|
2 | a| 9.15 | 9.30 | 0.30 | $10
3 | b| 9.30 | 9.45 | 0.15 | $10
4 | e|10.30 | - |#######|-




note : there are (a-n)-14 cabins. in a instance only one cabin will be alloted to a user.
In above example cabin a and b are free now but there exist a user in e

for ref the formula in D id =(C$-B$)

for the above scenario i need to know
question no 1:
--------------
i need to know how many cabins are free (not filled with customers) i know nothing about macros. can any one help me

question no 2:
--------------
referring above the rate shoul be automatically calculated as follows
1 - 30min - $10
30min - 1Hour - $20
ie for every 30 min I charge 10bugs ( I live in India)

any geeks to help ...thanks in advance
This message was edited by pravin_av on 2002-10-08 13:45

 

[bolo]

Hmmm i don't think calling someone a geek is the best way to ask for help do you?

 

[Dave Patton]

Number available with a simple formula

=14-(COUNTIF(B1:B100,">0")-COUNTIF(C1:C100,">0"))

If times are in cells

d2 =(C2-B2)*24

e2 =CEILING(D2,0.5)*20

Use Custom Number Format to add your "R".
With calculations that yield 0,
- modify the formula to yield "" or
- use custome format number or
- set option to hide 0
This message was edited by Dave Patton on 2002-10-08 19:27

 

[njbeancounter]

Question 2 is solved by using the vlookup function.
Length of time rate
0:00 0:30 10
0:31 1:00 20
1:01 1:30 30
1:31 2:00 40
2:01 2:30 50
2:31 3:00 60

 

[Yogi Anand]

Hi Pravin_av:

Please note the formulas for computations for Chargable time, Charge in Rs, and the Number of Booths available at any given point in time in the following worksheet simulation.

y021008.xls
	A	B	C	D	E	F	G	H
1	cabinNo	TimeIn	TimeOut	NetTime	Charge
2	a	9:00	9:30	0:30	10Rs		NumberofBoothsAvailable	13
3	b	9:30	9:45	0:15	10Rs
4	c	10:30					labels
5	d	23:00	1:00	2:00	40Rs		CafOperatorInput
6	e						computed
7	f
8	g
9	h
10	i
11	j
12	k
13	l
14	m
15	n
Sheet5

Regards!

Yogi

 

[pravin_av]

Hello Everybody,


My heartfelt gratitude to every one who helped me, to mention here dave,njbean and especially Mr.Yogi Anand thanks a ton.
I'll try every bit of your suggestions and let you know about the proceedings.



bolo kindly note:Geek,to me I thought was a expert thats all . i apologize if I did hurt you.

See you soon friends
Thanks once again for the help.
Pravin