Microsoft Excel - Extracting numerator and denominator from a fraction

Asked By Jessica
02-Aug-10 07:06 PM
I have several work sheets in several work books in which i am trying to compile the data from. In each work book, there are 6 work sheets, one for each day of the week, plus a weekly average sheet. In the weekly average sheet, it averages the number of points each person can earn for each day worked, though not everyone works all 5 days. I want to make a monthly and quarterly workbook that will average the number of points recieved by the number of days actually worked, but I dont want to have to add up each day when I already have them added for each week. I would like to be able to extract the numerator from each weekly average and add them together, then extract the denomenators and add them together to get my total points earned and total days worked. I've tried a few formulas, but excel just reduces my fraction to a whole number and there is no "/" in a whole number.

I am specifically looking to get the information from this formula:

=(Monday!C34+Tuesday!C34+Wednesday!C34+Thursday!C34+Friday!C34)/C2

eg i want to know that "=(Monday!C34+Tuesday!C34+Wednesday!C34+Thursday!C34+Friday!C34)" is 475 and "C2" is 5

Jessica
  Super Man replied to Jessica
02-Aug-10 11:07 PM
if you want to get answer as 475 and 5, then use

B1 => =(Monday!C34+Tuesday!C34+Wednesday!C34+Thursday!C34+Friday!C34)
c1  => =c2

if you want ansere like 475/5, then the numerator becames 95, and denominator  becames 1.
for getting answer 95, you can use this formula

=RIGHT(TEXT(A1,"? ?/?"),LEN(TEXT(A1,"? ?/?"))-FIND("/",TEXT(A1,"? ?/?")))

or

=RIGHT(TEXT(A1,"? ?/????????"),LEN(TEXT(A1,"? ?/????????"))-FIND("/",TEXT(A1,"? ?/????????")))
  Super Man replied to Jessica
02-Aug-10 11:08 PM
numerator
=LEFT(TEXT(A1,"???/???"),FIND("/",TEXT(A1,"???/???"))-1)+0

denominator
=RIGHT(TEXT(A1,"???/???"),FIND("/",TEXT(A1,"???/???"))-1)+0
Create New Account
help
How to delete a workbook in Microsoft Excel Excel Excel New Users Discussions Microsoft Excel (1) Workbook (1) VBA (1) You mean delete a file using excel? If yes, then you can
Secure OleDb Connection to a Microsoft Excel Workbook .NET Framework Good morning all, I want to password protect a Microsoft Excel Workbook containing all my reference tables for a program, so that I can place the workbook in a central location that is accessible by all, but so they can not wreck it. I am accessing the workbook via an OleDbConnection through the System.Data.OleDb framework. I have looked a few different
How can I email 2 pages from an Excel workbook. Excel I have a long running Excel workbook but only need to email 2 pages from it each month. How can I select the pages I want to send without sending the whole workbook every month Microsoft Excel 2003 Excel Miscellaneous Discussions Microsoft Excel (1) Excel (1) Workbook (1) PetePages (1) Month (1
Microsoft Excel Excel I accidently saved a file to the wrong workbook in Excel and have lost the info in the workbook. Is there a way to unsave the data so that I can retrieve the correct data in the workbook? Excel Miscellaneous Discussions Microsoft Excel (1) Workbook (1) Ione (1) Unsave (1) Not without a backup
How do I delete a header in Microsoft Excel? Excel I would like to know how to delete a header in Microsoft Excel. Can you help? Excel Miscellaneous Discussions Microsoft Excel (1) Excel 2007 (1) Page (1) Workbook (1) SQRT (1) VBA (1) ShowFormula (1) Anne