Use IF statement to find specific criteria and somehow change current value to a new value

[cvercrus]

I'm trying to bypass manual manipulation for a range of data and was hoping that there may be an IF - THEN formula to do some conversion

The survey that I am having to complete specifies that they want for institutions to use a 4 point scale and find the AVERAGE HS GPA based on that scale. I have 66 students who were AP and Honor students in HS who's GPA equates above the 4.0 scale. I would like to capture these students by converting their GPA (Example 4.56) and treat it as a 4.0 (same concept of an A+ grade = A grade).


I currently wrote the following to meet the 4.0 GPA scale, but would like to include the other 66 students who's GPA are above the 4.0 scale and somehow have a formula to capture them.

=AVERAGE(IF(('F2013'!$G$2:$G$10000="UG")*('F2013'!$K$2:$K$10000="FF")*('F2013'!$P$2:$P$10000<>"NDU")*('F2013'!$BK$2:$BK$10000<=4)*('F2013'!$BK$2:$BK$10000<>""),'F2013'!$BK$2:$BK$10000))


The above formula gives me 3.28 for AVG GPA meeting the 4.0 scale, but if I were able to pick up the other 66 students, the AVG GPA would become 3.397 (3.40). The 3.397 was derived by manually creating a new column COPY/PASTE and change GPA above 4.0 equal to 4.0 and then Averaged the new GPA column together.


Is there a guru out there that can come up with a formula to append to my already functional formula that could incorporate an IF statement to lookup GPA >4 and <= 4.99 (column BK) and change the current value ONLY in the formula and not the raw data being extracted to 4.0 (to keep the original data submission in tact)? Again, I'm trying to bypass having to add any additional columns - using formulas to automatically calculate datasets when a new flat file is added to my spreadsheet that will automatically compute criteria within seconds upon import.

Please advise and Thx in advance! Cris

[vamosj]

Looking through this I have a couple of questions before I can help out. First off is the instance that someone on the 5.0 scale having a GPA lower than 4.0, I know this is possible hopefully them being in AP they would keep it above that, but if there was one on that scale below 4.0, what do you use to show what scale that student is based on? Was also wondering what the UG, FF, and NDU had in your equation? Last but not least would be if you could load up an example spreadsheet (cleaned of any student names/info) that we can use to better assist you with.

[cvercrus]

1) scale at institution is not defined since the majority of the transcripts received do not specify whether they are weighted or not. Received confirmation that any HS GPA > 4.0 (I.e. 4.01 to 4.99) will be treated as a 4.0 value and any 5.0 or above will be omitted from the criteria.

Therefore, I just need to convert those 4.01 to 4.99 to 4.0.

2) UG = Undergraduate; FF = First-Time Freshmen; NDU = Non-Degree Undergraduate


Thx vamosj for the inquiry.

Semper-Fi!

[vamosj]

Okay, what I came up with is definitely not pretty but I think it will definitely do the trick.. When I looked at your formula I couldn't reason out why you were multiplying True/False answers against each other so from what I could understand is that you are only averaging individuals who had "UG" or "FF" listed in column G as long as column K wasn't = "NDU" so correct me if I am wrong on that.

Since we needed to break apart the 4.0 and 5.0 groups here is an explenation of my formula.

SUMIF #1 Finds the sum of anyone who has "UG" and a GPA of <4 but doesn't have "NDU"
SUMIF #2 Finds the sum of anyone who has "FF" and a GPA of <4 but doesn't have "NDU"
COUNTIFS #1 Finds the amount of students who have "UG" and a GPA >4 (but less than 5), then multiplies that number by 4. Basically giving us a Sum of all GPA's > 4
COUNTIFS #2 Finds the amount of students who have "FF" and a GPA >4 (but less than 5), then multiplies that number by 4. Basically giving us a Sum of all GPA's > 4

Division Then this will divide the above Sum by the total number of students that met the criteria

COUNTIFS #3 Counts number of students who have "UG" and a GPA of < 5 but doesn't have "NDU"
COUNTIFS #4 Counts number of students who have "FF" and a GPA of < 5 but doesn't have "NDU"

=(SUMIFS('F2013'!BK:BK,'F2013'!G:G,"UG",'F2013'!P:P,"<>NDU",'F2013'!BK:BK,"<=4")+SUMIFS('F2013'!BK:BK,'F2013'!G:G,"FF",'F2013'!P:P,"<>NDU",'F2013'!BK:BK,"<=4")+COUNTIFS('F2013'!BK:BK,">4",'F2013'!G:G,"UG",'F2013'!P:P,"<>NDU",'F2013'!BK:BK,">4",'F2013'!BK:BK,"<5")*4+COUNTIFS('F2013'!BK:BK,">4",'F2013'!G:G,"FF",'F2013'!P:P,"<>NDU",'F2013'!BK:BK,">4",'F2013'!BK:BK,"<5")*4)/((COUNTIFS('F2013'!BK:BK,"<5",'F2013'!G:G,"UG",'F2013'!P:P,"<>NDU",'F2013'!BK:BK,"<5"))+(COUNTIFS('F2013'!BK:BK,"<5",'F2013'!G:G,"FF",'F2013'!P:P,"<>NDU",'F2013'!BK:BK,"<5")))

In Short
((Sum of GPA <=4) + (Sum of 4 < GPA > 5)) / (Number of students that met criteria) = Your Average GPA

[cvercrus]

YOOOUUU'RREE AWESOME!!! This works great and I got the same value doing it manually. Keep warm and be safe!

[vamosj]

Thank you


Please add to my reputation. Only takes a few seconds.. Click on the "* Add Reputation" and let me know how I did.