If the teachers are free and students pay isn’t the total to be paid the entry fee times the number of students?
Trading Addins For XL  Excel Addins  Excel Templates  Excel Web Pages
Scheduling In Excel  Home/Construction Estimating In XL  Print VBA Code  Sparkmaker
Merge Excel The Easy Way

Code VBA 
Build Automated Trading Models In Excel
Excel Training  Excel Dashboard Reports
Excel Development/Consultancy  FREE  Ozgrid Excel Newsletter
Hi,
I know pretty much nothing about excel. I'm trying to create a quote... basically I need to find out about calculating ratios.
We have 47 students going on a trip to different venues, we offer 1:10 teachers (i.e. one teacher free, for every ten students going), however some venues offer a different ratio e.g. 1:5 teachers free of charge.
I need to somehow create a column where it will calculate the amount charged for the different ratios the venues offered.
I hope this makes sense as I'm finding it hard to explain!
Thanks,
Erin
If the teachers are free and students pay isn’t the total to be paid the entry fee times the number of students?
Bill
Tip: To avoid chasing code always use Option Explicit.
Sorry, I'll try to explain it a little better but i'm a little confused myself.
Say we have 47 students going and 6 teachers, based on our company's 1:10 ratio, 4 would be free of charge and 2 would have to pay.
However, they're going to a venue that offers 1:20 free of charge, so we need to calculate the amount we need to charge the school as only 2 teachers would be free of charge, and 4 would need to pay.
I was hoping to somehow create a column where you'd enter the ratio beside the venue, and it would calculate the amounts for you. I suppose it could be done based on a percentage but we were hoping to get some sort of ratio formula.
The simplest way would be to divide the amount of students attending by single ratio number.
i.e. 47/10 = 4.7
therefore with 47 students divided by the ratio of 1:10 there would need to be 4.7 teachers (or round up to 5)
i.e. 47/5 = 9.5
therefore with 47 students divided by the ratio of 1:5 there would need to be 9.4 teachers (or round up to 10)
Hope this helps
Joel PJ
Auto Merged Post;
Sorry posted before reading your reply...Originally Posted by Joel PJ
If the above makes sense then you would need to take the amount of teachers and take away the result.
i.e. if 6 teachers are going and 4.7 is the ratio result.
Then once you round up the 4.7 to 5, one teacher would have to pay.
Does this make sense?
Last edited by Joel PJ; September 25th, 2007 at 11:07. Reason: Auto Merged Doublepost
See attached sample
BTW Wlecomr to oz
Last edited by Bill Rockenbach; May 5th, 2009 at 10:13.
Bill
Tip: To avoid chasing code always use Option Explicit.
I think I get what you mean, however the numbers that we have are based on the numbers the school give us.
So they're saying to us 'the final numbers going are 47 students and 6 teachers so give us a quote for those figures' and we have to create a quote, with the seperate amounts charged for each venue the schools visit.
Say if they go to the Sydney Opera House for a tour, and say the venue charges 1:20, we have to calculate the amount charged, which would work out to be 47 students and 4 paying teachers.
Next venue could be the National Museum of Australia and say they charge at 1:10, we'd have to calculate it at 47 students and 2 paying teachers.
What we want is somewhere to put the different ratios next to each venue, so it would automatically calculate the rates we would be charged.
I hope that makes sense!
Say ezza,
Do you require that at least as many teachers per the ratio attend the venue? If not there could be fewer teachers than the total free teachers allowed.
I mortified the spreadsheet to consider this in the calculations.
See attached sample.
Last edited by Bill Rockenbach; May 5th, 2009 at 10:13.
Bill
Tip: To avoid chasing code always use Option Explicit.
Thanks very much for the help so far.
I'll show you an example of how the quotes look at the moment. As i'm pretty terrible with excel (and maths!) it's kind of hard to get my head around it all.
Bill, thanks for the attachments! They've made more sense to me then what i'm working with now. We don't require any number of teachers to attend, just how many the school specifies is going.
MAybe this attachment will help
Last edited by Bill Rockenbach; May 5th, 2009 at 10:13.
Bill
Tip: To avoid chasing code always use Option Explicit.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks