Home |
Search |
Today's Posts |
|
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|