Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
conversion formula
hi I work with Excel 2000. I have a spreadsheet that holds the pass marks
for students displayed like so 27-Apr-07 El Health & Safety 13 93/130 72% 29-May-07 First Aid 18 124/180 69% 08-Jun-07 Geography 19 156/190 82% this shows date, name of course, how many students sat the exam, what their pass mark was and then the pass mark as a persentage. The person who set up this spreadsheet obviously has better math skills than me as she was just typint in the pass mark persentage. I need to know if there is a formula that will automatically show me what the persentage rate is. (my fingers are crossed in the hope that there is) Thank you for reading my post. Hopefully you can answer my querie |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
conversion formula
Ok sorry about this but i have managed to figure it out. Now i am wondering
if there is any way that i can get the percentage value to show up automaticaly into a different cell without me having to type it in as i am not allowed to break up the pass mark into 2 different cells. -- Thank you for reading my post. Hopefully you can answer my querie "Shazza" wrote: hi I work with Excel 2000. I have a spreadsheet that holds the pass marks for students displayed like so 27-Apr-07 El Health & Safety 13 93/130 72% 29-May-07 First Aid 18 124/180 69% 08-Jun-07 Geography 19 156/190 82% this shows date, name of course, how many students sat the exam, what their pass mark was and then the pass mark as a persentage. The person who set up this spreadsheet obviously has better math skills than me as she was just typint in the pass mark persentage. I need to know if there is a formula that will automatically show me what the persentage rate is. (my fingers are crossed in the hope that there is) Thank you for reading my post. Hopefully you can answer my querie |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
conversion formula
=LEFT(D5,FIND("/",D5)-1)/MID(D5,FIND("/",D5)+1,99)
-- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Shazza" wrote in message ... hi I work with Excel 2000. I have a spreadsheet that holds the pass marks for students displayed like so 27-Apr-07 El Health & Safety 13 93/130 72% 29-May-07 First Aid 18 124/180 69% 08-Jun-07 Geography 19 156/190 82% this shows date, name of course, how many students sat the exam, what their pass mark was and then the pass mark as a persentage. The person who set up this spreadsheet obviously has better math skills than me as she was just typint in the pass mark persentage. I need to know if there is a formula that will automatically show me what the persentage rate is. (my fingers are crossed in the hope that there is) Thank you for reading my post. Hopefully you can answer my querie |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
conversion formula
Try in column E (assuming the ratio 93/130 etc is in column D):
=ROUND(LEFT(D2,FIND("/",D2)-1)/RIGHT(D2,LEN(D2)-FIND("/",D2)),2) Format cell as % with zero (0) decimal places HTH "Shazza" wrote: Ok sorry about this but i have managed to figure it out. Now i am wondering if there is any way that i can get the percentage value to show up automaticaly into a different cell without me having to type it in as i am not allowed to break up the pass mark into 2 different cells. -- Thank you for reading my post. Hopefully you can answer my querie "Shazza" wrote: hi I work with Excel 2000. I have a spreadsheet that holds the pass marks for students displayed like so 27-Apr-07 El Health & Safety 13 93/130 72% 29-May-07 First Aid 18 124/180 69% 08-Jun-07 Geography 19 156/190 82% this shows date, name of course, how many students sat the exam, what their pass mark was and then the pass mark as a persentage. The person who set up this spreadsheet obviously has better math skills than me as she was just typint in the pass mark persentage. I need to know if there is a formula that will automatically show me what the persentage rate is. (my fingers are crossed in the hope that there is) Thank you for reading my post. Hopefully you can answer my querie |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
conversion formula
Oh you wonderful person thank you very very much x
-- "Toppers" wrote: Try in column E (assuming the ratio 93/130 etc is in column D): =ROUND(LEFT(D2,FIND("/",D2)-1)/RIGHT(D2,LEN(D2)-FIND("/",D2)),2) Format cell as % with zero (0) decimal places HTH "Shazza" wrote: Ok sorry about this but i have managed to figure it out. Now i am wondering if there is any way that i can get the percentage value to show up automaticaly into a different cell without me having to type it in as i am not allowed to break up the pass mark into 2 different cells. -- Thank you for reading my post. Hopefully you can answer my querie "Shazza" wrote: hi I work with Excel 2000. I have a spreadsheet that holds the pass marks for students displayed like so 27-Apr-07 El Health & Safety 13 93/130 72% 29-May-07 First Aid 18 124/180 69% 08-Jun-07 Geography 19 156/190 82% this shows date, name of course, how many students sat the exam, what their pass mark was and then the pass mark as a persentage. The person who set up this spreadsheet obviously has better math skills than me as she was just typint in the pass mark persentage. I need to know if there is a formula that will automatically show me what the persentage rate is. (my fingers are crossed in the hope that there is) Thank you for reading my post. Hopefully you can answer my querie |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
conversion formula
You can use this formula:
=LEFT(J51, FIND("/", J51, 1)-1)/RIGHT(J51, FIND("/", J51, 1)) "Bob Phillips" wrote: =LEFT(D5,FIND("/",D5)-1)/MID(D5,FIND("/",D5)+1,99) -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Shazza" wrote in message ... hi I work with Excel 2000. I have a spreadsheet that holds the pass marks for students displayed like so 27-Apr-07 El Health & Safety 13 93/130 72% 29-May-07 First Aid 18 124/180 69% 08-Jun-07 Geography 19 156/190 82% this shows date, name of course, how many students sat the exam, what their pass mark was and then the pass mark as a persentage. The person who set up this spreadsheet obviously has better math skills than me as she was just typint in the pass mark persentage. I need to know if there is a formula that will automatically show me what the persentage rate is. (my fingers are crossed in the hope that there is) Thank you for reading my post. Hopefully you can answer my querie |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
conversion formula
That only works because co-incidentally the / is in position 3 and there are
3 numbers after it Neither 101/130 or 81/90 will work with that formula. -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Excel_Learner" wrote in message ... You can use this formula: =LEFT(J51, FIND("/", J51, 1)-1)/RIGHT(J51, FIND("/", J51, 1)) "Bob Phillips" wrote: =LEFT(D5,FIND("/",D5)-1)/MID(D5,FIND("/",D5)+1,99) -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Shazza" wrote in message ... hi I work with Excel 2000. I have a spreadsheet that holds the pass marks for students displayed like so 27-Apr-07 El Health & Safety 13 93/130 72% 29-May-07 First Aid 18 124/180 69% 08-Jun-07 Geography 19 156/190 82% this shows date, name of course, how many students sat the exam, what their pass mark was and then the pass mark as a persentage. The person who set up this spreadsheet obviously has better math skills than me as she was just typint in the pass mark persentage. I need to know if there is a formula that will automatically show me what the persentage rate is. (my fingers are crossed in the hope that there is) Thank you for reading my post. Hopefully you can answer my querie |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
conversion formula
Did you mean to say
=LEFT(J51, FIND("/", J51, 1)-1)/RIGHT(J51, LEN(J51) - FIND("/", J51, 1)) ? -- David Biddulph "Excel_Learner" wrote in message ... You can use this formula: =LEFT(J51, FIND("/", J51, 1)-1)/RIGHT(J51, FIND("/", J51, 1)) "Bob Phillips" wrote: =LEFT(D5,FIND("/",D5)-1)/MID(D5,FIND("/",D5)+1,99) -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Shazza" wrote in message ... hi I work with Excel 2000. I have a spreadsheet that holds the pass marks for students displayed like so 27-Apr-07 El Health & Safety 13 93/130 72% 29-May-07 First Aid 18 124/180 69% 08-Jun-07 Geography 19 156/190 82% this shows date, name of course, how many students sat the exam, what their pass mark was and then the pass mark as a persentage. The person who set up this spreadsheet obviously has better math skills than me as she was just typint in the pass mark persentage. I need to know if there is a formula that will automatically show me what the persentage rate is. (my fingers are crossed in the hope that there is) Thank you for reading my post. Hopefully you can answer my querie |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
conversion formula
You are right Bob
we can use this also: =LEFT(I19,FIND("/",I19,1)-1)/RIGHT(I19, LEN(I19)-LEN(RIGHT(I19, FIND("/", I19,1)))) it is a bit lenghty. I couldn't understand why did you take 99 in Mid function. Could you please explain that. thank a lot "Excel_Learner" wrote: You can use this formula: =LEFT(J51, FIND("/", J51, 1)-1)/RIGHT(J51, FIND("/", J51, 1)) "Bob Phillips" wrote: =LEFT(D5,FIND("/",D5)-1)/MID(D5,FIND("/",D5)+1,99) -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Shazza" wrote in message ... hi I work with Excel 2000. I have a spreadsheet that holds the pass marks for students displayed like so 27-Apr-07 El Health & Safety 13 93/130 72% 29-May-07 First Aid 18 124/180 69% 08-Jun-07 Geography 19 156/190 82% this shows date, name of course, how many students sat the exam, what their pass mark was and then the pass mark as a persentage. The person who set up this spreadsheet obviously has better math skills than me as she was just typint in the pass mark persentage. I need to know if there is a formula that will automatically show me what the persentage rate is. (my fingers are crossed in the hope that there is) Thank you for reading my post. Hopefully you can answer my querie |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
conversion formula
I wanted all of the remaining characters after the /. I could have used
RIGHT subtracting the position of the / from the LEN of the string. I chose to use MID with a character count of 99 because if you use a number of characters in the MID function greater than the possible number of characters it doesn't choke, it just returns as many as it can. Same result as the RIGHT function, less code. -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Excel_Learner" wrote in message ... You are right Bob we can use this also: =LEFT(I19,FIND("/",I19,1)-1)/RIGHT(I19, LEN(I19)-LEN(RIGHT(I19, FIND("/", I19,1)))) it is a bit lenghty. I couldn't understand why did you take 99 in Mid function. Could you please explain that. thank a lot "Excel_Learner" wrote: You can use this formula: =LEFT(J51, FIND("/", J51, 1)-1)/RIGHT(J51, FIND("/", J51, 1)) "Bob Phillips" wrote: =LEFT(D5,FIND("/",D5)-1)/MID(D5,FIND("/",D5)+1,99) -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Shazza" wrote in message ... hi I work with Excel 2000. I have a spreadsheet that holds the pass marks for students displayed like so 27-Apr-07 El Health & Safety 13 93/130 72% 29-May-07 First Aid 18 124/180 69% 08-Jun-07 Geography 19 156/190 82% this shows date, name of course, how many students sat the exam, what their pass mark was and then the pass mark as a persentage. The person who set up this spreadsheet obviously has better math skills than me as she was just typint in the pass mark persentage. I need to know if there is a formula that will automatically show me what the persentage rate is. (my fingers are crossed in the hope that there is) Thank you for reading my post. Hopefully you can answer my querie |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Currency Conversion Formula | Excel Worksheet Functions | |||
Conversion Formula | Excel Worksheet Functions | |||
Automatic Formula to Value Conversion | Excel Worksheet Functions | |||
Need Formula for Date Conversion | Excel Worksheet Functions | |||
Conversion lookup formula | Excel Worksheet Functions |