Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Page 1 of 2 1 2 LastLast
Results 1 to 10 of 12

Thread: Calculate Ratio

  1. #1
    Join Date
    25th September 2007
    Posts
    5

    Calculate Ratio

    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

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    17th July 2004
    Location
    Texas, USA
    Posts
    1,939

    Re: Creating A Ratio Formula

    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.

  3. #3
    Join Date
    25th September 2007
    Posts
    5

    Re: Creating A Ratio Formula

    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.

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    22nd March 2006
    Posts
    65

    Re: Creating A 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;

    Quote Originally Posted by Joel PJ
    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
    Sorry posted before reading your reply...

    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

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    17th July 2004
    Location
    Texas, USA
    Posts
    1,939

    Re: Creating A Ratio Formula

    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.

  6. #6
    Join Date
    25th September 2007
    Posts
    5

    Re: Creating A Ratio Formula

    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!

    Excel Video Tutorials / Excel Dashboards Reports


  7. #7
    Join Date
    17th July 2004
    Location
    Texas, USA
    Posts
    1,939

    Re: Creating A Ratio Formula

    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.

  8. #8
    Join Date
    24th January 2003
    Location
    Australia
    Posts
    31,717

  9. #9
    Join Date
    25th September 2007
    Posts
    5

    Re: Calculate Ratio

    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.
    Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.

    Excel Video Tutorials / Excel Dashboards Reports


  10. #10
    Join Date
    17th July 2004
    Location
    Texas, USA
    Posts
    1,939

    Re: Calculate Ratio

    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.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Ratio Calculation
    By lyambor in forum EXCEL HELP
    Replies: 9
    Last Post: September 6th, 2012, 06:32
  2. employee turnover ratio report
    By Dmiele1 in forum EXCEL HELP
    Replies: 26
    Last Post: August 18th, 2006, 14:53
  3. Turnover ratio report
    By Dmiele1 in forum Excel and/or SQL Help
    Replies: 1
    Last Post: August 16th, 2006, 03:39
  4. Replies: 3
    Last Post: June 9th, 2003, 09:23

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
porno