Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Hellp Me Excel Help!!!!
ok, i have used a countif formula to work out how many days are accounted for!.
now i want to create another formula where it adds up the days as well as adds the price of the day to it. i have tried an if statement and countifs. but i dont no wot formula i need to be able to do this. the low down... im creating a spreadsheet data base for a nursery. and there are 3 sessions Morning, Afternoon and all day. i did a countif formula to work out the days attentended. now each session has a different rate all day being 34, morning £20 and afternoon £15. now i was to link the countif formula to these prices. Thank you |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Hellp Me Excel Help!!!!
You'll need to tell us how you identify a session as Morning, Afternoon or
All Day. -- Biff Microsoft Excel MVP "headbanging_Fe" wrote in message ... ok, i have used a countif formula to work out how many days are accounted for!. now i want to create another formula where it adds up the days as well as adds the price of the day to it. i have tried an if statement and countifs. but i dont no wot formula i need to be able to do this. the low down... im creating a spreadsheet data base for a nursery. and there are 3 sessions Morning, Afternoon and all day. i did a countif formula to work out the days attentended. now each session has a different rate all day being 34, morning £20 and afternoon £15. now i was to link the countif formula to these prices. Thank you |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Hellp Me Excel Help!!!!
i use "D" for day, "M" for morning, "A" afternoon.
this is the formula: =COUNTIF(G12:K12, "D")+COUNTIF(G12:K12, "M")+COUNTIF(G12:K12,"A") if i have a child the attends 5 full days the it should work out that price. if a child atttends a morning afternoon and all day then the formula is should work out the different prices for all. so "M" is £20, "D" is £34 and "A" is £15 "T. Valko" wrote: You'll need to tell us how you identify a session as Morning, Afternoon or All Day. -- Biff Microsoft Excel MVP "headbanging_Fe" wrote in message ... ok, i have used a countif formula to work out how many days are accounted for!. now i want to create another formula where it adds up the days as well as adds the price of the day to it. i have tried an if statement and countifs. but i dont no wot formula i need to be able to do this. the low down... im creating a spreadsheet data base for a nursery. and there are 3 sessions Morning, Afternoon and all day. i did a countif formula to work out the days attentended. now each session has a different rate all day being 34, morning £20 and afternoon £15. now i was to link the countif formula to these prices. Thank you |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Hellp Me Excel Help!!!!
Try this:
=SUM(COUNTIF(G12:K12,{"D","M","A"})*{34,20,15}) -- Biff Microsoft Excel MVP "headbanging_Fe" wrote in message ... i use "D" for day, "M" for morning, "A" afternoon. this is the formula: =COUNTIF(G12:K12, "D")+COUNTIF(G12:K12, "M")+COUNTIF(G12:K12,"A") if i have a child the attends 5 full days the it should work out that price. if a child atttends a morning afternoon and all day then the formula is should work out the different prices for all. so "M" is £20, "D" is £34 and "A" is £15 "T. Valko" wrote: You'll need to tell us how you identify a session as Morning, Afternoon or All Day. -- Biff Microsoft Excel MVP "headbanging_Fe" wrote in message ... ok, i have used a countif formula to work out how many days are accounted for!. now i want to create another formula where it adds up the days as well as adds the price of the day to it. i have tried an if statement and countifs. but i dont no wot formula i need to be able to do this. the low down... im creating a spreadsheet data base for a nursery. and there are 3 sessions Morning, Afternoon and all day. i did a countif formula to work out the days attentended. now each session has a different rate all day being 34, morning £20 and afternoon £15. now i was to link the countif formula to these prices. Thank you |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Hellp Me Excel Help!!!!
would this do the same.
"T. Valko" wrote: Try this: =SUM(COUNTIF(G12:K12,{"D","M","A"})*{34,20,15}) -- Biff Microsoft Excel MVP "headbanging_Fe" wrote in message ... i use "D" for day, "M" for morning, "A" afternoon. this is the formula: =COUNTIF(G12:K12, "D")+COUNTIF(G12:K12, "M")+COUNTIF(G12:K12,"A") if i have a child the attends 5 full days the it should work out that price. if a child atttends a morning afternoon and all day then the formula is should work out the different prices for all. so "M" is £20, "D" is £34 and "A" is £15 "T. Valko" wrote: You'll need to tell us how you identify a session as Morning, Afternoon or All Day. -- Biff Microsoft Excel MVP "headbanging_Fe" wrote in message ... ok, i have used a countif formula to work out how many days are accounted for!. now i want to create another formula where it adds up the days as well as adds the price of the day to it. i have tried an if statement and countifs. but i dont no wot formula i need to be able to do this. the low down... im creating a spreadsheet data base for a nursery. and there are 3 sessions Morning, Afternoon and all day. i did a countif formula to work out the days attentended. now each session has a different rate all day being 34, morning £20 and afternoon £15. now i was to link the countif formula to these prices. Thank you |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Hellp Me Excel Help!!!!
would this do the same.
Do the same as what? ????? -- Biff Microsoft Excel MVP "headbanging_Fe" wrote in message ... would this do the same. "T. Valko" wrote: Try this: =SUM(COUNTIF(G12:K12,{"D","M","A"})*{34,20,15}) -- Biff Microsoft Excel MVP "headbanging_Fe" wrote in message ... i use "D" for day, "M" for morning, "A" afternoon. this is the formula: =COUNTIF(G12:K12, "D")+COUNTIF(G12:K12, "M")+COUNTIF(G12:K12,"A") if i have a child the attends 5 full days the it should work out that price. if a child atttends a morning afternoon and all day then the formula is should work out the different prices for all. so "M" is £20, "D" is £34 and "A" is £15 "T. Valko" wrote: You'll need to tell us how you identify a session as Morning, Afternoon or All Day. -- Biff Microsoft Excel MVP "headbanging_Fe" wrote in message ... ok, i have used a countif formula to work out how many days are accounted for!. now i want to create another formula where it adds up the days as well as adds the price of the day to it. i have tried an if statement and countifs. but i dont no wot formula i need to be able to do this. the low down... im creating a spreadsheet data base for a nursery. and there are 3 sessions Morning, Afternoon and all day. i did a countif formula to work out the days attentended. now each session has a different rate all day being 34, morning £20 and afternoon £15. now i was to link the countif formula to these prices. Thank you |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Hellp Me Excel Help!!!!
Try this
=(COUNTIF(G12:K12,"D")*34)+(COUNTIF(G12:K12,"M")*2 0)+(COUNTIF(G12:K12,"A")*15) Best Stew "headbanging_Fe" wrote: ok, i have used a countif formula to work out how many days are accounted for!. now i want to create another formula where it adds up the days as well as adds the price of the day to it. i have tried an if statement and countifs. but i dont no wot formula i need to be able to do this. the low down... im creating a spreadsheet data base for a nursery. and there are 3 sessions Morning, Afternoon and all day. i did a countif formula to work out the days attentended. now each session has a different rate all day being 34, morning £20 and afternoon £15. now i was to link the countif formula to these prices. Thank you |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Hellp Me Excel Help!!!!
awww stew thank you so much. do you know long i have been trying to that for!
so the prices only now relate the letter it is in the bracket to. Thank You "stew" wrote: Try this =(COUNTIF(G12:K12,"D")*34)+(COUNTIF(G12:K12,"M")*2 0)+(COUNTIF(G12:K12,"A")*15) Best Stew "headbanging_Fe" wrote: ok, i have used a countif formula to work out how many days are accounted for!. now i want to create another formula where it adds up the days as well as adds the price of the day to it. i have tried an if statement and countifs. but i dont no wot formula i need to be able to do this. the low down... im creating a spreadsheet data base for a nursery. and there are 3 sessions Morning, Afternoon and all day. i did a countif formula to work out the days attentended. now each session has a different rate all day being 34, morning £20 and afternoon £15. now i was to link the countif formula to these prices. Thank you |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Hellp Me Excel Help!!!!
I have been helped so many times on this site that its good to give it back
for someone else Best Stew "headbanging_Fe" wrote: awww stew thank you so much. do you know long i have been trying to that for! so the prices only now relate the letter it is in the bracket to. Thank You "stew" wrote: Try this =(COUNTIF(G12:K12,"D")*34)+(COUNTIF(G12:K12,"M")*2 0)+(COUNTIF(G12:K12,"A")*15) Best Stew "headbanging_Fe" wrote: ok, i have used a countif formula to work out how many days are accounted for!. now i want to create another formula where it adds up the days as well as adds the price of the day to it. i have tried an if statement and countifs. but i dont no wot formula i need to be able to do this. the low down... im creating a spreadsheet data base for a nursery. and there are 3 sessions Morning, Afternoon and all day. i did a countif formula to work out the days attentended. now each session has a different rate all day being 34, morning £20 and afternoon £15. now i was to link the countif formula to these prices. Thank you |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
Hellp Me Excel Help!!!!
i done the previous problem and thank you to everyone.
i have to make a discount on if attendance is 5 days then they get 15% and if the attendance is 4 days 10% and less 0. =(COUNTIF(G12:K12,"D")*34,if=G12:K125-0.15,if=G12:K12<=4,-0.10,0)+(COUNTIF(G12:K12,"M")*20)+(COUNTIF(G12:K12 ,"A")*15) i used the countif statement to work out the formula, but it doesnt work. im thinking off doing an if statement to see if it works. =IF(G25<4, (10/100),(15/100))-M25 this one is giving me the right answer but the answer is with a minus. the formula is meant to say when the children attend less than four days takeaway 10% otherwise 15%. |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
Hellp Me Excel Help!!!!
Hi Again
Put this in the cell beside the Countif Formula ( I Assume L12) and head it as Discount. I think it does the Job. There is always other ways. =IF(L12=170,(L12)*15%,IF(L12=136,(L12)*10%,"No Discount")) Best Stewart "headbanging_Fe" wrote: i done the previous problem and thank you to everyone. i have to make a discount on if attendance is 5 days then they get 15% and if the attendance is 4 days 10% and less 0. =(COUNTIF(G12:K12,"D")*34,if=G12:K125-0.15,if=G12:K12<=4,-0.10,0)+(COUNTIF(G12:K12,"M")*20)+(COUNTIF(G12:K12 ,"A")*15) i used the countif statement to work out the formula, but it doesnt work. im thinking off doing an if statement to see if it works. =IF(G25<4, (10/100),(15/100))-M25 this one is giving me the right answer but the answer is with a minus. the formula is meant to say when the children attend less than four days takeaway 10% otherwise 15%. |
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
Hellp Me Excel Help!!!!
thanks,
that one just puts down no discount. i tried out a couple formulas =M25-IF(G25<4, (10/100),(15/100)) =M26-IF(G26<=4, (10/100),IF(G26:K26=5,15/100,0)) =(COUNTIF(G12:K12,"D")*34)+(COUNTIF(G12:K12,"M")*2 0)+(COUNTIF(G12:K12,"A")*15)-0.15 these formulas work but it is taking off some money when it shouldnt. but im sure it has to be a if statement for it to use the 3 conclusions. "stew" wrote: Hi Again Put this in the cell beside the Countif Formula ( I Assume L12) and head it as Discount. I think it does the Job. There is always other ways. =IF(L12=170,(L12)*15%,IF(L12=136,(L12)*10%,"No Discount")) Best Stewart "headbanging_Fe" wrote: i done the previous problem and thank you to everyone. i have to make a discount on if attendance is 5 days then they get 15% and if the attendance is 4 days 10% and less 0. =(COUNTIF(G12:K12,"D")*34,if=G12:K125-0.15,if=G12:K12<=4,-0.10,0)+(COUNTIF(G12:K12,"M")*20)+(COUNTIF(G12:K12 ,"A")*15) i used the countif statement to work out the formula, but it doesnt work. im thinking off doing an if statement to see if it works. =IF(G25<4, (10/100),(15/100))-M25 this one is giving me the right answer but the answer is with a minus. the formula is meant to say when the children attend less than four days takeaway 10% otherwise 15%. |
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
Hellp Me Excel Help!!!!
What cell contains the gross figure, in Money, before discount?
Stew "headbanging_Fe" wrote: thanks, that one just puts down no discount. i tried out a couple formulas =M25-IF(G25<4, (10/100),(15/100)) =M26-IF(G26<=4, (10/100),IF(G26:K26=5,15/100,0)) =(COUNTIF(G12:K12,"D")*34)+(COUNTIF(G12:K12,"M")*2 0)+(COUNTIF(G12:K12,"A")*15)-0.15 these formulas work but it is taking off some money when it shouldnt. but im sure it has to be a if statement for it to use the 3 conclusions. "stew" wrote: Hi Again Put this in the cell beside the Countif Formula ( I Assume L12) and head it as Discount. I think it does the Job. There is always other ways. =IF(L12=170,(L12)*15%,IF(L12=136,(L12)*10%,"No Discount")) Best Stewart "headbanging_Fe" wrote: i done the previous problem and thank you to everyone. i have to make a discount on if attendance is 5 days then they get 15% and if the attendance is 4 days 10% and less 0. =(COUNTIF(G12:K12,"D")*34,if=G12:K125-0.15,if=G12:K12<=4,-0.10,0)+(COUNTIF(G12:K12,"M")*20)+(COUNTIF(G12:K12 ,"A")*15) i used the countif statement to work out the formula, but it doesnt work. im thinking off doing an if statement to see if it works. =IF(G25<4, (10/100),(15/100))-M25 this one is giving me the right answer but the answer is with a minus. the formula is meant to say when the children attend less than four days takeaway 10% otherwise 15%. |
#14
Posted to microsoft.public.excel.misc
|
|||
|
|||
Hellp Me Excel Help!!!!
its from m12 - m41 is before the discount. i can get it to work for the 5 days n then - 15% but not of fours day and three and less. Fe "stew" wrote: What cell contains the gross figure, in Money, before discount? Stew "headbanging_Fe" wrote: thanks, that one just puts down no discount. i tried out a couple formulas =M25-IF(G25<4, (10/100),(15/100)) =M26-IF(G26<=4, (10/100),IF(G26:K26=5,15/100,0)) =(COUNTIF(G12:K12,"D")*34)+(COUNTIF(G12:K12,"M")*2 0)+(COUNTIF(G12:K12,"A")*15)-0.15 these formulas work but it is taking off some money when it shouldnt. but im sure it has to be a if statement for it to use the 3 conclusions. "stew" wrote: Hi Again Put this in the cell beside the Countif Formula ( I Assume L12) and head it as Discount. I think it does the Job. There is always other ways. =IF(L12=170,(L12)*15%,IF(L12=136,(L12)*10%,"No Discount")) Best Stewart "headbanging_Fe" wrote: i done the previous problem and thank you to everyone. i have to make a discount on if attendance is 5 days then they get 15% and if the attendance is 4 days 10% and less 0. =(COUNTIF(G12:K12,"D")*34,if=G12:K125-0.15,if=G12:K12<=4,-0.10,0)+(COUNTIF(G12:K12,"M")*20)+(COUNTIF(G12:K12 ,"A")*15) i used the countif statement to work out the formula, but it doesnt work. im thinking off doing an if statement to see if it works. =IF(G25<4, (10/100),(15/100))-M25 this one is giving me the right answer but the answer is with a minus. the formula is meant to say when the children attend less than four days takeaway 10% otherwise 15%. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|