Adding Fractions and Displaying as a percentage.

[SarahN]

Hi,

I am trying to add a number of Fractions and then display as a percentage.
Does anyone know a formula that will automatically add a number of cells and
display the total as a percentage?



4/4 4/4 3/3 2/2 2/2 = 100%
4/4 4/4 3/4 2/2 2/2 = ?


Thanks heaps in advance.

 

[Jacob Skaria]

Try this UDF (User Defined function). From workbook launch VBE using Alt+F11.
From menu Insert a Module and paste the below function.Close and get back to
workbook and try the below formula.

=EVAL(A1:E1)
and format the formula cell to %

Function Eval(rngTemp As Range) As Variant
Dim cell As Range
For Each cell In rngTemp
Eval = Eval + Evaluate("=" & cell.Text)
Next
Eval = Eval / rngTemp.Count
End Function

 

[Sarah Norrie]

Thankyou. I think this has solved my issue.

I have no idea what it means but it seems to be converting them.



Jacob Skaria wrote:

Try this UDF (User Defined function). From workbook launch VBE using Alt+F11.
29-Apr-10

Try this UDF (User Defined function). From workbook launch VBE using Alt+F11.
From menu Insert a Module and paste the below function.Close and get back to
workbook and try the below formula.

=EVAL(A1:E1)
and format the formula cell to %

Function Eval(rngTemp As Range) As Variant
Dim cell As Range
For Each cell In rngTemp
Eval = Eval + Evaluate("=" & cell.Text)
Next
Eval = Eval / rngTemp.Count
End Function

--
Jacob (MVP - Excel)


:

Previous Posts In This Thread:


Submitted via EggHeadCafe - Software Developer Portal of Choice
Using VSTO Add-In To Automate Frequent Excel 2007 Tasks
http://www.eggheadcafe.com/tutorial...60-39a86ccab5d6/using-vsto-addin-to-auto.aspx

 

[SarahN]

ok so I think that I have missed some information. When using 4/4 i am
meaning 4 out of 4. 0/2 is 0 out of 2. So in the last line it would total
13/14. this would be about 93% not 80%. I hope I have explained what I am
trying to acheivce and this can help.

4/4 4/4 3/4 2/2 2/2 95%
3/4 1/2 0/2 2/2 2/2 65%
3/4 4/4 0/1 2/2 1/1 75%
4/4 4/4 3/3 2/2 0/1 80%

 

[Jacob Skaria]

OK.. Try the below

=SUMPRODUCT(--LEFT(A1:E1,FIND("/",A1:E1)-1))/
SUMPRODUCT(--MID(A1:E1,FIND("/",A1:E1)+1,10))

The 1st part will give the sum of numerics before "/" and the second part
give the sum of numerics after "/"...If you have problems try out the two
sumproduct formulas separately

 

[SarahN]

That worked. Thankyou

OK.. Try the below

=SUMPRODUCT(--LEFT(A1:E1,FIND("/",A1:E1)-1))/
SUMPRODUCT(--MID(A1:E1,FIND("/",A1:E1)+1,10))

The 1st part will give the sum of numerics before "/" and the second part
give the sum of numerics after "/"...If you have problems try out the two
sumproduct formulas separately

 

[Jacob Skaria]

Thanks for the feedback..