|
NEED HELP WITH FORMULAS
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 "SUNSHINE31" wrote in message ... 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
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 -- Regards Frank Kabel Frankfurt, Germany "SUNSHINE31" schrieb im Newsbeitrag ... 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 |
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! :) "JulieD" wrote: 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 "SUNSHINE31" wrote in message ... 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 Sunshine31
you can then use something like this: =COUNTIF(B2:F2,"3am")*20+COUNTIF(B2:F2,"3pm")*29+C OUNTIF(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 "SUNSHINE31" wrote in message ... 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! :) "JulieD" wrote: 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 "SUNSHINE31" wrote in message ... 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 |
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! "JulieD" wrote: Hi Sunshine31 you can then use something like this: =COUNTIF(B2:F2,"3am")*20+COUNTIF(B2:F2,"3pm")*29+C OUNTIF(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 "SUNSHINE31" wrote in message ... 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! :) "JulieD" wrote: 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 "SUNSHINE31" wrote in message ... 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 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",2 0,IF(B2="3pm",29,IF(B2="both",39,0)))......amount paid.....=D2-C2 Cheers julieD "SUNSHINE31" wrote in message ... 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! "JulieD" wrote: Hi Sunshine31 you can then use something like this: =COUNTIF(B2:F2,"3am")*20+COUNTIF(B2:F2,"3pm")*29+C OUNTIF(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 "SUNSHINE31" wrote in message ... 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! :) "JulieD" wrote: 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 "SUNSHINE31" wrote in message ... 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 |
| All times are GMT +1. The time now is 05:38 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com