If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Adding Fractions and Displaying as a percentage.
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. |
#2
|
|||
|
|||
Adding Fractions and Displaying as a percentage.
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) "SarahN" wrote: 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. |
#3
|
|||
|
|||
Yay
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) "SarahN" wrote: 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/tutorials...n-to-auto.aspx |
#4
|
|||
|
|||
Yay
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% "Sarah Norrie" wrote: 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) "SarahN" wrote: 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/tutorials...n-to-auto.aspx . |
#5
|
|||
|
|||
Yay
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 (MVP - Excel) "SarahN" wrote: 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% "Sarah Norrie" wrote: 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) "SarahN" wrote: 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/tutorials...n-to-auto.aspx . |
#6
|
|||
|
|||
Yay
That worked. Thankyou
"Jacob Skaria" wrote: 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 (MVP - Excel) "SarahN" wrote: 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% "Sarah Norrie" wrote: 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) "SarahN" wrote: 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/tutorials...n-to-auto.aspx . |
#7
|
|||
|
|||
Yay
Thanks for the feedback..
-- Jacob (MVP - Excel) "SarahN" wrote: That worked. Thankyou "Jacob Skaria" wrote: 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 (MVP - Excel) "SarahN" wrote: 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% "Sarah Norrie" wrote: 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) "SarahN" wrote: 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/tutorials...n-to-auto.aspx . |
Thread Tools | |
Display Modes | |
|
|