+ Reply to Thread
Results 1 to 8 of 8

Google Sheets: test with wheights system (weighted average with multiple variables)

  1. #1
    Registered User
    Join Date
    01-10-2021
    Location
    Brasil
    MS-Off Ver
    Web
    Posts
    4

    Question Google Sheets: test with wheights system (weighted average with multiple variables)

    This year I will study for a national exam and hope to be approved for computer science. Excited, so I decided to plan my objectives considering the grades I expected to achieve on the essay and mathematics. Thus I made the following google spreadsheet. If your solution uses anything that google sheets doesn't support, I totally agree to move my sheets to excel. OK?

    College exam help.png

    Notice above on lines 1-2 that I selected the desired course, determined the required average - last year's minimun grade - and the sum of the 5 grades that I need.

    Going down, on lines 3-9, there is my first attempt to provide the grades I expect to attain and get an estimate of the remaining grades for the blank cells on column C (Input). This consists simply of subtracting the total input (C9) from the passing score sum (D2) and then dividing by the number of blank cells on column C (countblank(C4:C8)). For this I just used a simple IF(input<>"" ; repeat ; calculate) to recognize when an input is filled or blank, if not blank the formula just repeats the input, if so it calculates.

    At the end, everything went well and I got the result 711,6666667 divided equally by the 3 blank cells I left on the Input column.

    --/--/--

    Now the problem:

    Later, I figured out that my brother wants to enter on a college that has a weights system, then I tried to implement it to my sheet just by adding a weights column where all the cells are required to be filled with numbers from 1-100 (notice that his college uses weights from 1-5, but I'm not sure if anyone would use anything higher than 5). But, after failing miserably, I noticed that I can't just divide equally if the equation results on a weighted average with multiple variables and it must also be taken into account that there are several combinations.

    I can't even decide how many calculated columns I need.

    If it was required to fill all the inputs or leave only 1 blank cell, I could get an easy result. However my intention is to provide only the grades on the subjects you have affinity with - essay and math in my case.

    Is there any formula that could solve this problem?

    --/--/--

    I have been using sheets for a year, but this is my first time with formulas.
    Last edited by PhilEdward; 01-10-2021 at 10:04 PM.

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,415

    Re: College entrance test with wheights system (weighted average with multiple variables)

    Welcome to the forum.

    Is this an Excel or Google Sheets query?
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  3. #3
    Registered User
    Join Date
    01-10-2021
    Location
    Brasil
    MS-Off Ver
    Web
    Posts
    4

    Re: College entrance test with wheights system (weighted average with multiple variables)

    Quote Originally Posted by AliGW View Post
    Welcome to the forum.

    Is this an Excel or Google Sheets query?
    Many thnaks! This was made on google sheets. But I think that it could apply to Excel, since I don't use vba, macros or any programming, just formulas.

  4. #4
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,415

    Re: College entrance test with wheights system (weighted average with multiple variables)

    That’s not what I asked. Which will you be using with this file? The platform will determine the solutions offered.

  5. #5
    Registered User
    Join Date
    01-10-2021
    Location
    Brasil
    MS-Off Ver
    Web
    Posts
    4

    Re: College entrance test with wheights system (weighted average with multiple variables)

    Quote Originally Posted by AliGW View Post
    That’s not what I asked. Which will you be using with this file? The platform will determine the solutions offered.
    I'll be using google sheets.
    T-T Sorry. I'm having some translations and keyboard problems which keeps swapping words instead of correcting grammar.
    I couldn't find any translation for "query", so I'm assuming that it is a feature that works in different manners on Google and Excel. Is that right?

  6. #6
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,803

    Re: Google Sheets: test with wheights system (weighted average with multiple variables)

    I find that I must understand the calculation outside of Excel (or other programming language) before I can program the calculation into the spreadsheet. It looks like weighted input should be the product of weight and input (so E11=C11*D11 and copied down). I could not tell how you intended to calculate the objective column. Should it be the same as the unweighted example above (results 900, 711 2/3, 750, 711 2/3, 711 2/3)? If so, I would expect the same formula (references adapted) should work in F11:F15. If not, we probably need a better description of what goes here. I'm also not sure what the calculation should look like in the weighted objective column, though I might guess that it should be the product of the objective and weight (So, G11=C11*F11).

    If you can help us understand the arithmetic that should go into these cells, we should be able to help with spreadsheet formulas (at this point, I'm not expecting to need anything special that is unique to Google Sheets) for these.
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  7. #7
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,811

    Re: Google Sheets: test with wheights system (weighted average with multiple variables)

    crossposted: https://www.mrexcel.com/board/thread...ystem.1158047/

    Rule 03: Cross-posting Without Telling Us

    Your post does not comply with Rule 3 of our Forum RULES. Do not cross-post your question on multiple forums without telling us about your threads on other forums.

    Post a link to any other forums where you have asked the same question.

    Cross-posting is when you post the same question in other forums on the web. The last thing you want to do is waste people's time working on an issue you have already resolved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser) to the cross-post.

    Read this to understand why we ask you to do this.

    I have added the crosspost reference for you today. Please comply with this and all our rules in the future
    Alan עַם יִשְׂרָאֵל חַי


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

  8. #8
    Registered User
    Join Date
    01-10-2021
    Location
    Brasil
    MS-Off Ver
    Web
    Posts
    4

    Re: Google Sheets: test with wheights system (weighted average with multiple variables)

    Quote Originally Posted by MrShorty View Post
    I find that I must understand the calculation outside of Excel (or other programming language) before I can program the calculation into the spreadsheet. It looks like weighted input should be the product of weight and input (so E11=C11*D11 and copied down). I could not tell how you intended to calculate the objective column. Should it be the same as the unweighted example above (results 900, 711 2/3, 750, 711 2/3, 711 2/3)? If so, I would expect the same formula (references adapted) should work in F11:F15. If not, we probably need a better description of what goes here. I'm also not sure what the calculation should look like in the weighted objective column, though I might guess that it should be the product of the objective and weight (So, G11=C11*F11).

    If you can help us understand the arithmetic that should go into these cells, we should be able to help with spreadsheet formulas (at this point, I'm not expecting to need anything special that is unique to Google Sheets) for these.
    I was organizing some visual explanation for your questions then I accidentaly got the solution T-T ;-; u-u
    The keys were to figure out the total sum of the weighted objective, then getting the difference between it and the total sum of weighted input, then divide it by the sum of weights with blank inputs (wich in this case is 3+2+2 = 7) and multiply it by the weight of its line.

    Attachment 712592

    Attachment 712593

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Weighted Average with Multiple Variables
    By sasponaugle in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 10-13-2019, 04:56 PM
  2. Weighted average across multiple sheets
    By thezwashere in forum Excel General
    Replies: 2
    Last Post: 10-11-2018, 10:37 AM
  3. Replies: 0
    Last Post: 02-15-2018, 03:04 AM
  4. Conditional Weighted Average with Multiple Variables
    By Chellobi in forum Excel Formulas & Functions
    Replies: 15
    Last Post: 10-04-2017, 11:42 AM
  5. Weighted Average for multiple sheets
    By walkingcow in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 04-12-2016, 01:34 PM
  6. Weighted Average accounting for 2 variables??
    By dz6kb4 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 11-19-2013, 08:35 AM
  7. Weighted Average with multiple variables
    By bigtoad in forum Excel General
    Replies: 4
    Last Post: 02-27-2011, 04:07 PM

Tags for this Thread

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1