Picking out the numerator and denominator

[dwojtowi]

Hello,
I need to input numbers as a fraction for each month, but then i nee
to yearly number to be sum(numerators)/sum(denominators) to give a
accurate ytd.

For example, if they entered a 1/10 in month one, and 20/100 in mont
two, the ytd should equal 21/110.

Is there a way to pick the numerators and denominators out?

Thank you very much
Dav

 

[Frank Kabel]

Hi
to make this easiere I'd suggest the following:
1. select the cell(s)
2. goto 'Format - Cells' and choose 'fraction' as format (hope I
translated this correctly)

Now you can simp,y enter 1/10 and Excel will store this as a value.
These values can easily be added

 

[mdalby]

Why don't you take a different approach.

Have a column that displays the numbers in a fraction but you enter th
data in seperate columns for the numerator and the denominator

e.g.

A1 = 1
B1 = 5
C1 = =A1&"/"&B1

Then you have the data that you can sum up for your numerator an
denominator.

M

 

[dwojtowi]

I have considered the multiple row approach, but the problem is I hav
about 60 rows, in this sheet, and that would triple my number of rows
and it just wouldnt "look nice". If this was simply a data analysi
tool I would do that, but this is also part of a report that get
submitted and posted throughout the offic

 

[Guest]

Hi dwojtowi,
I assumed that you are inputting the original numbers as text. The following formula will strip out, and add up, the numerators and denominators, from cells A1 through A10.

=SUM(VALUE(LEFT(A1:A10,FIND("/",A1:A10)-1)))&"/"&SUM(VALUE(RIGHT(A1:A10,LEN(A1:A10)-FIND("/",A1:A10))))

This need to be array entered, hit control-shift-enter instead of enter. the formula should appear with { } around it.

Good Luck,
Mark Graesser
(e-mail address removed)
Boston MA

----- dwojtowi > wrote: -----

I have considered the multiple row approach, but the problem is I have
about 60 rows, in this sheet, and that would triple my number of rows,
and it just wouldnt "look nice". If this was simply a data analysis
tool I would do that, but this is also part of a report that gets
submitted and posted throughout the office

 

[dwojtowi]

Thank you soo much, that is so close to being what i need. I am no
very familiar with working with arrays, so there are a couple of thing
that I cannot figure out...

How can I modify this so it skips blank cells (Now if there is a blan
cell in the row, it returns a #Value error)?
How can I have this return a decimal value??
Thanks,
Dav

 

[Frank Kabel]

Hi
1. You may change the formula to
=SUM(VALUE(LEFT(A1:A10,FIND("/",A1:A10)-1))) /
SUM(VALUE(RIGHT(A1:A10,LEN(A1:A10)-FIND("/",A1:A10))))
to get a decimal value

2. For your error: caused by FIND as it did not find the '/' in this
cell and returns an error. Try (not tested)
=SUM(VALUE(LEFT(A1:A10,IF(ISERROR(FIND("/",A1:A10)),0,FIND("/",A1:A10))
-1))) /
SUM(VALUE(RIGHT(A1:A10,LEN(A1:A10)-IF(ISERROR(FIND("/",A1:A10)),0,FIND(
"/",A1:A10)))))

 

[Frank Kabel]

and for the second one to return a string and not a decimal use
=SUM(VALUE(LEFT(A1:A10,IF(ISERROR(FIND("/",A1:A10)),0,FIND("/",A1:A10))
-1))) & "/" &
SUM(VALUE(RIGHT(A1:A10,LEN(A1:A10)-IF(ISERROR(FIND("/",A1:A10)),0,FIND(
"/",A1:A10)))))

--
Regards
Frank Kabel
Frankfurt, Germany

Hi
1. You may change the formula to
=SUM(VALUE(LEFT(A1:A10,FIND("/",A1:A10)-1))) /
SUM(VALUE(RIGHT(A1:A10,LEN(A1:A10)-FIND("/",A1:A10))))
to get a decimal value

2. For your error: caused by FIND as it did not find the '/' in this
cell and returns an error. Try (not tested)
=SUM(VALUE(LEFT(A1:A10,IF(ISERROR(FIND("/",A1:A10)),0,FIND("/",A1:A10))
-1))) /
SUM(VALUE(RIGHT(A1:A10,LEN(A1:A10)-IF(ISERROR(FIND("/",A1:A10)),0,FIND(
"/",A1:A10)))))