Fractions: summing numerators and denomerators separately
 

I have a sheet with several rows like this:

Jan Feb Mar Apr Year to Date

1/3 4/5 0/9 2/6 etc. 7/23

Where Year to Date (YTD) is the sum of all the numerators and
denominators. Right now these fractions are formatted as text and I
have to calculate the YTD by hand. Can anybody think of a way to
automate this? I have tried simply putting the num. and den. in
separate cells, one above the other, but then they don't look like
fractions and the intended audience of the report expects the fraction
format. We could teach them to read the new format but I'd like to
avoid that if I can. Thanks for any suggestions!


--
burnsbyrne
------------------------------------------------------------------------
burnsbyrne's Profile: http://www.excelforum.com/member.php...o&userid=22153
View this thread: http://www.excelforum.com/showthread...hreadid=374128

Hi

It's a three stage process.

1. In A1, B1, C1, D1 etc. put your text fractions as you have done.
2. A2 = LEFT(A1), B2 = LEFT(B1), C2 = LEFT(C1) etc.
3. A3 = VALUE(A2), B3 = VALUE(B2), C3 = VALUE(C3) etc.
Now you can sum row A3.

Do the same for the denominator and you're there.
Actually you would probably want to combine the second and third
columns. ie A2=VALUE(LEFT(A1))

Enjoy


--
MartinShort

Software Tester
------------------------------------------------------------------------
MartinShort's Profile: http://www.excelforum.com/member.php...o&userid=22034
View this thread: http://www.excelforum.com/showthread...hreadid=374128

Thanks for the solution! I guess I need to learn more about using
formulas with text. While this solution will work handily, I wonder
whether there is a way to do this without adding two extra rows for
each row of fractions. The sheets and reports containing these
fractions are several pages long and the fraction rows occur about
every three rows. The fractions represent the number of hospital
patients who had a treatment over the number of patients who were
elegible for the treatment. The docs like to see the data expressed as
a fraction.
Thanks for your help. I'll see what I can do with the VALUE and RIGHT
functions. I could also look at hiding some rows before printing the
report.


--
burnsbyrne
------------------------------------------------------------------------
burnsbyrne's Profile: http://www.excelforum.com/member.php...o&userid=22153
View this thread: http://www.excelforum.com/showthread...hreadid=374128

Two things I would suggest:

1. Condense your helper row from 2 rows to 1 by nesting your formula as
I suggested ie *= A2=VALUE(LEFT(A1))*

2. A feature of Excel that a lot of people don't know exist is on the
edit menu - Custom Views. You can set this up to exactly what you want
including print options.


--
MartinShort

Software Tester
------------------------------------------------------------------------
MartinShort's Profile: http://www.excelforum.com/member.php...o&userid=22034
View this thread: http://www.excelforum.com/showthread...hreadid=374128

Martin,
Thanks again. I'll give your suggestions a go at work tomorrow.
Mike Burns


--
burnsbyrne


------------------------------------------------------------------------
burnsbyrne's Profile: http://www.excelforum.com/member.php...o&userid=22153
View this thread: http://www.excelforum.com/showthread...hreadid=374128