NEED HELP WITH FORMULAS

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I AM TRYING TO SET UP A FORMULA FOR THIS INFO, SAY A CHILD GOES TO SCHOOL 3
DAYS AM ONLY THE CHARGE IS 20.00 SO IF I PUT 3AM, IT SHOULD PUT 20.00 IN THE
CELL AND SO ON? I CAN'T FIGURE IT OUT. THOUGHT THE MATCH FORMULA WOULD WORK
BUT I NEED HELP WITH SPECIFICS...PLEASE!!!!!!! HELP!~!

Destyne
Carl
Tyler
Campbell
Tony
Thunder

3 days
Am only 20.00
Pm only 29.00
Am and pm 39.00

4 days
Am only 22.00
Pm only 32.00
am and pm 42.00
 
Hi

it's a bit hard to figure out how you've set up the worksheet and how you
want to match up 3AM with the structure that you've given (ie how do you
enter both the am & pm one).

Personally, i would use data validation and VLOOKUP to achieve this sort of
thing, by creating a table on a sheet e.g.

Code Cost
3am 20
3pm 29
3both 39
4am 22
4pm 32
4both 42

(i.e. range A1 to B7), then select A1 to A7 and choose insert / name /
create - top row to create a named range called "code". Then select from A1
to B7, click in the name box (to the left of the formula bar) and type
att_rates and press ENTER to create another named range called att_rates.

then select the area where i wanted to choose the code for each child and
choose data / validation - under settings, choose allow LIST, click in the
source line, press F3 and choose CODE. click OK. This will create a drop
down list for each child where you can choose the appropriate attendance
code.

Then were i wanted the cost, i would type the formula
=VLOOKUP(B1,att_rates,2,0)
where B1 was the drop down box of the first child i wanted to find the cost
for.
This formula will then return the associated cost.

Hope this helps
Cheers
JulieD
 
Hi
first: please turn off your CAPS Lock: Difficult to read and considered as
SHOUTING in newsgroups
Second: you may give some more information:
- what kind of data have you already entered in your spreadsheet
- which cells do you use for your data
- maybe some example rows of your data
 
I was looking for something a little more simple...for example...
column 1 will have names of students, column 2 will list week 1 column 3
will list week2 and so on for 5 weeks. Then the last column will reflect the
total. So if i enter in 3am in every column for all 5 weeks, then it should
give me a total of 100.00(that is 20.00 per week). i would like to enter both
if they came in am and pm...does that make more sense? thanks for the help! :)
 
Hi Sunshine31

you can then use something like this:

=COUNTIF(B2:F2,"3am")*20+COUNTIF(B2:F2,"3pm")*29+COUNTIF(B2:F2,"3both")*39

which says, count the number of times 3am occurs within the range B2:F2 and
times that number by 20, then add the number of times "3pm" occurs within
the same range after multiplying that number by 29 etc.

Hope this helps
Cheers
JulieD
 
Julie -

Thank you for that is there a formula where i can have a column titled,
name, days attended, cost, paid, balance? how would i set that up so that i
can calculate instead of the week thing...still putting in 3am for the days
attended and so on! :) thanks you have been awesome!
 
Hi Sunshine

sorry about the delay in replying (we've got bushfires up here and the
power's not been that reliable) - can't visualise how you can have days
attended and put in the 3am do you mean

Name.....Days Attended.....Cost....Paid.....Balance
Bill.........3am....................=IF(b2="3am",20,IF(B2="3pm",29,IF(B2="both",39,0)))......amount
paid.....=D2-C2

Cheers
julieD
 
Back
Top