Announcement

Collapse
No announcement yet.

Calculate Ratio

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • 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

  • #2
    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.

    Comment


    • #3
      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.

      Comment


      • #4
        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;

        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, 11:07. Reason: Auto Merged Doublepost

        Comment


        • #5
          Re: Creating A Ratio Formula

          See attached sample

          BTW Wlecomr to oz
          Last edited by Bill Rockenbach; May 5th, 2009, 10:13.
          Bill
          Tip: To avoid chasing code always use Option Explicit.

          Comment


          • #6
            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!

            Comment


            • #7
              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, 10:13.
              Bill
              Tip: To avoid chasing code always use Option Explicit.

              Comment


              • #9
                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

                Comment


                • #10
                  Re: Calculate Ratio

                  MAybe this attachment will help
                  Last edited by Bill Rockenbach; May 5th, 2009, 10:13.
                  Bill
                  Tip: To avoid chasing code always use Option Explicit.

                  Comment


                  • #11
                    Re: Calculate Ratio

                    Looks brilliant, thanks very much

                    I tried copying and pasting it into my working spreadsheet and it came up with a '#VALUE!' error...

                    Comment


                    • #12


                      Re: Calculate Ratio

                      the ratio column must have a valid ratio or you will get the #value message.
                      Bill
                      Tip: To avoid chasing code always use Option Explicit.

                      Comment

                      Working...
                      X