Adding Numerators from fractions

[JrayK2]

I am adding numerators from fractions and currently using this formula:

=SUMPRODUCT(--(LEFT(0&I35:I38,FIND("/",0&I35:I38)-1)))

25/20

20/20

19/20

19/20

<colgroup><col></colgroup><tbody>
</tbody>

83

I want my formula to cap out at the denominator however, for example on my first cell IF numerator is more than denominator, use denominator.
Can anyone help?

 

[sunny102]

Hi,

Can you explain a bit further by giving one more example as to exactly what you need help with?

Thanks

 

[JrayK2]

Say I have 25/20, 25/20, 10/20, 10/20.

With the current formula I would get 70.
But I don't want to add the numerator past 100%, or greater than denominator. I'm looking to get 60 as result.

 

[JoeMo]

Say I have 25/20, 25/20, 10/20, 10/20.

With the current formula I would get 70.
But I don't want to add the numerator past 100%, or greater than denominator. I'm looking to get 60 as result.

Here's a UDF you can try with an example using your data shown below.

Function SumTopExcept(R As Range) As Double
'sums numerators of cells containing fractions except if numerator > denominator in any cell,
'the denominator is used in the sum.
Dim c As Range, Parts As Variant, S As Double
For Each c In R
    If InStr(c.Value, "/") Then
        Parts = Split(c.Value, "/")
        If Parts(0) > Parts(1) Then
            S = S + Val(Parts(1))
        Else
            S = S + Val(Parts(0))
        End If
    End If
Next c
SumTopExcept = S
End Function

Sheet1

	A	B
1	25/20	60
2	25/20
3	10/20
4	10/20

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:64px;"><col style="width:64px;"></colgroup><tbody>
</tbody>

Spreadsheet Formulas

Cell Formula B1 =SumTopExcept(A1:A4) <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

 

[mikerickson]

=MAX(SUMPRODUCT(--(LEFT(0&I35:I38,FIND("/",0&I35:I38)-1))), 20) should do what you want.

or
=MAX(SUMPRODUCT(--(LEFT(0&I35:I38,FIND("/",0&I35:I38)-1))), MID(I35, 1+ FIND("/",I35&"/"),255)+0)