Excel fractions filter: add cells, display as fraction, add fractions

January 12, 2017 10:21 AM

I'm converting an old hand-tally chart into Excel, but I'm unsure how to translate a simple portion/total fraction of cells into Excel and then add more fractions in that row. "D / (B+C)," basically.

The purpose of the hand-tally chart is keeping track of customer interactions at a station: total number of interactions, how many interactions were face-to-face versus by phone, how many of the total interactions included special order, displaying the total in fraction form as (special orders / total number of interactions) then adding those totals to other station totals. This is simple to do on the paper chart because it only involves addition, but I don't know to make it display as a row in Excel without the program trying to divide my fractions and such. Here's my example:

Station Blue interactions on Day 1
B1 Face-to-face: 47
C1 Phone: 3
D1 Special orders: 0
E1 Blue total: 0/50 (special orders/total orders)

Station Red interactions on Day 1
F1 Face-to-face: 10
G1 Phone: 0
H1 Special orders: 3
I1 Red total: 3/10

J1 Grand total for Day 1: 3/60 (special orders/total orders)

Meanwhile, Column A is keeping track of dates, and at Day/Row 32 there's the sum of each column for the month. Columns E, I, and J are all displayed as fractions.

How do I use formulas to calculate:
E1 = D1 / (B1+C1)
I1 = H1 / (F1+G1)
J1 = (D1+H1) / (B1+C1+F1+G1)

then total them at the bottom of their columns?

posted by nicebookrack to Computers & Internet (9 answers total)

Best answer: Do you need Excel to "know" that E1, I1, and J1 are fractions? If not, and they only need to be human-readable, you can treat it as a string.

E.g., for E1, you have

=D1&"/"&B1+C1

I1 would be

=H1&"/"&F1+G1

and J1 would be

=D1+H1&"/"&B1+C1+F1+G1

For the totals, you'll need to total the colums for the raw data (B, C, D, F, G, H) then compute the "fractions" from the totals.

You couldn't compute the "total" fractions from the individual fractions if they were true fractions because it wouldn't be enough information. Consider the following two examples

Day 1    2  2  6   6/10
Day 2    3  2  5   5/10
Total    5  4 11  11/20

Day 1    2  2  6   6/10
Day 2    1  0  1   1/ 2
Total    3  2  7   7/12

But the "Day 2" fractions in these examples are equal (5/10 = 1/2) but the total factions are not (11/20=0.55, 7/12≈0.58); ergo, you can't compute the total fraction directly from the individual fractions alone; you have to sum the numerator and denominator separately.
posted by DevilsAdvocate at 10:44 AM on January 12, 2017

Response by poster: Nope, the fractions only need to be human-readable, not "seen" by Excel.
posted by nicebookrack at 11:05 AM on January 12, 2017

Excel does have a fraction format:

Excel tutorial: How to use fraction formatting in Excel

which seems like it should work (using the 2 or 3 digit option for denominator)?
posted by Boobus Tuber at 11:18 AM on January 12, 2017

Response by poster: I may need to insert more columns or something to display the (special orders/total orders) together without making them "fractions," because it makes no sense to string a row's cells together but then have to manually total all the rows' numerators and denominators separately, like DevilsAdvocate says. Hmmm.
posted by nicebookrack at 11:21 AM on January 12, 2017

Note that the fraction formatting Boobus Tuber suggests will reduce a fraction to lowest terms; in your example J1 (3/60) would be displayed as 1/20. This may or may not be desirable.
posted by DevilsAdvocate at 11:25 AM on January 12, 2017

Response by poster: Not desirable to reduce the fractions, no. Like DA said, they're not real "fractions;" they're just my easy visual stand-in for "Out of 60 total interactions, 3 total were special orders."
posted by nicebookrack at 11:32 AM on January 12, 2017

Best answer: Personally, I'd keep discrete data in separate cells and display fractions using three cells across:
Data A as a right justified cell, narrow cell containing "/", Data B as a left justified cell.

DataA / DataB

I'm sure there may be fancier ways of doing it, but I think this will be the most intelligible to go back to and understand later.
posted by meinvt at 11:40 AM on January 12, 2017

Response by poster: If I split the Total columns (E, I, J) into separate columns, is it possible to create strings to make the entire column sum cells from their rows automatically, using a wildcard(*)? Or must I string each cell separately? Something like,
Grand Special Order Total: J*=D*+H*, versus Row 1 J1 = (D1+H1), Row 2 J2 = (D2+H2), etc.
posted by nicebookrack at 11:46 AM on January 12, 2017

Best answer: Enter the formula for J1 then "grab" the lower right corner of J1 (click-hold) and drag down to fill J2 - J32. The formulas will automatically update.
posted by DevilsAdvocate at 12:05 PM on January 12, 2017