Conditionally sum fractional cells based on column headings

I think you need to study your maths again.
Whn adding fractions you can't just add the top numbers and the bottom numbers together, ie

1/3 + 4/12 is not 1+4 divided by 3 + 12, ie 5 / 15 That's wrong! 5 /15 is 1/3, so how can 1/3 + another non zero number still be 1/3 ???
Fractions don't work like that.

You need to find a common denominator to add them.
In the above the common denominator is 12
So 1/3 = 4 /12
So 4 /12 + 4 /12 = 8 / 12 = 2 / 3

Google "adding fractions"

Administrative Note:

We would very much like to help you with your query, however it has been brought to our attention that the same query has been posted on one or more other forums and you have not told us about this. You are required to do so. Cross-posts are allowed but you must provide a link to your posts on other sites.
For new members: you will not be able to post a full URL, so remove the https://www. section and add one or two breaks to the rest of the address. A Moderator or Admin will then fix the link for you.

Please see Forum Rule #7 about cross-posting and adjust accordingly. Read this to understand why we (and other sites like us) consider this to be important: https://excelguru.ca/a-message-to-forum-cross-posters/

(Note: this requirement is not optional. No help to be offered until you provide a link or, for members with fewer than 10 posts, a comment telling us where else you have posted this query.)

Here's a link:

https://www.mrexcel.com/board/thread...dings.1267547/

Are you still using Excel 2007?

I am well aware of how fractions work, and I explained the requirement in the original post.

The problem is that Excel is seeing the scores as fractions, so for example 1/3 is actually 0.33333...

I would encourage you (as a retired teacher) to change the way that you enter scores. Add a row below the unit name and add the maximum score for each test. In the actual score cells, give just the score and not what it's out of.

For example, in M7:

Please Login or Register  to view this content.

AliGW on MS365 Beta Channel (Windows 11) 64 bit

	G	H	I	J	K	L	M
5	OA.2	OA.3	OA.3	OA.2	OA.3	OA.4	OA.2
6	3	6	4	5	3	8
7	1	5	4	2	2	3	3/8

Sheet: Sheet1

I think you need to study your maths again.

Maybe he means this

1/3 means 1 out of 3 questions answered correctly
2/5 means 2 out of 5 questions answered correctly


Together :
3/8 3 out of 8 questions answered correctly

That is correct, but Excel sees 1/3 as 0.33333 recurring, and not as a text entry of 1/3. Hence my suggested workaround above.

Hi AliGW - I tried this formula but for some reason it is resulting in 0.7333/0.7333 for the values with 'OA.2'. I would expect this to result to 3/8 instead. I've attached the updated Excel file (excel_sample2.xlsx) with this formula for you.

Try

Formula:

Please Login or Register  to view this content.

Results in M & N are fractions

Hi, I tried this but the math doesn't seem to add up correctly for OA.3 (or for OA.2 if I change any of the existing values). Based on the below values, for OA.3, it should be a total of 11/13



OA.2 OA.3 OA.3 OA.2 OA.3 OA.4 OA.2 OA.3
2/3 5/6 4/4 2/5 2/3 3/8 1/2 5/6

Also if I switch the Value in G6 (for first OA.2 column) to be 2/3 , then the total for OA.2 changes from 3/8 (correct) to 1/2 (incorrect). What are your thoughts?

I see a mix of numbers and text (unit fractions like 4/4 get stored as text strings). All examples also show ratios where both numerator and denominator are less than 10 (single digit). Assuming those two observaations are universally true, I:

1) An IF() function to test for text/number. If text, then copy the value down. If number, convert to text in "0/0" format. =IF(ISTEXT(G6),G6,TEXT(G6,"0/0")). In G8. Copy/paste/fill across.
2) Extract numerator from text and convert to number. =VALUE(LEFT(G8,1)) in G9. Copy/paste/fill across.
3) Extract denominator from text and convert to number. =VALLUE(RIGHT(G8,1)) in G10. copy/paste/fill across.
4) With numerator and denominators separated into separate rows, the conditional summations are easy with a SUMIFS() function. =SUMIFS($G9:$L9,$G$5:$L$5,M$5) in M9. Copy into M9:N10.
5) If it is necessary to combine into a single fraction, I would combine as text =M9&"/"&M10 in M11. Copy into N11.

It's basically AliGW's strategy of separating numerators and denominators so you can sum them independently. I haven't tried nesting that into a single cell function. Adjustment would need to be made for the possibility of 2 digit numerators/denominators.

Would something like that help?

Expanding on John's suggestion, this seems to work:

=SUMPRODUCT((LEFT($G6:$L6,1))*($G5:$L5=M$5))&"/"&SUMPRODUCT((RIGHT($G6:$L6,1))*($G5:$L5=M$5))

and so does this:

=SUMPRODUCT((LEFT($G6:$L6,FIND("/",$G6:$L6)-1))*($G5:$L5=M$5))&"/"&SUMPRODUCT((MID($G6:$L6,FIND("/",$G6:$L6)+1,4))*($G5:$L5=M$5))

The advantage here is that it won't matter how many digits are either side of the /.

I had this originally (as per AliGw's post)

Please Login or Register  to view this content.

but changed to try and avoid the answer being text (but did not spot the error in OA.3 ... my bad!)

These are not traditional fractions, so not treating them as text is never going to work. This is what I was trying to get at earlier in the thread.

The data is coming from another system and the end-users of the spreadsheet (Teachers ) don't want to do any manual manipulation of the data and just want to copy and paste it as-is.

I was a teacher and Head of Department for 30 years. Teachers are some if the best learners on the planet: if you take the time to explain the problem here and give them a simple template for data entry in the format I suggested, they will adapt perfectly well. It's all very well blaming them, but if you aren't willing or able to persuade them to adjust their data entry, then you are as much of the problem as they are. You are working in an educational environment, so educate them. I've been there and done it, so don't tell me it's not possible.

By the way, my subject was languages, and if a languages team can be converted, so can any department. You are setting yourself up for a whole world of pain when you want to start analysing this data. You really do need to bite the bullet and get them to change their practices instead of trying to work with improper data entry. And you need to park the 'us and them' attitide: one of the biggest problems in schools is when the IT team and the teaching staff (Heads of Department) don't work together as a team. Because I am a bit of an Excel nerd, I was fortunate enough to be able to help bridge that gap for the whole school: you need to find an ally on the teaching staff, preferably a manager or middle manager, and make it a bit of a crusade. As I said, I've been there and done it. Make it your New Year's resolution. Good luck!

And let it be noted that there is no more delicate matter to take in hand, nor more dangerous to conduct, nor more doubtful in its success, than to set up as a leader in the introduction of changes.

Machiavelli.

LOL! Well, one thing's for sure: if you don't try, you don't progress. Someone has to be the one to stick their head above the parapet, and I guess that was me in my career. It landed me with a lot of extra work, but I did effect change, and for the better, so I'm pretty proud of that. I think old Machiavelli was a bit of an extreme case - I wasn't in his league ...

I wholeheartedly agree with your post #22: and the key word is LEADER [the one to stick their head above the parapet] as you obviously were in your career.

Thanks - yes, I suppose I was. Certainly in the vanguard if not right in front!

TEXTBEFORE and TEXTAFTER are NOT available in 2021 - 2024 and 365 only.

Please bookmark this site for your future reference: https://bettersolutions.com/excel/fu...-functions.htm

Thanks for the links - my point is that the OP does NOT have 2024 (or 365), but 2021, which does not have those two functions. I don't need to know how to use them (I already know - I use 365), but thanks. I also offered an alternative in post #20.

New functions in 2021: https://bettersolutions.com/excel/fu...-functions.htm