Microsoft Excel 2007
December 25, 2009 at 05:27:10
Specs: Windows Vista
 iam programming a student reports and i need help in this mannermy question isi want the number 0.25 to be 0 onlyi want the number 0.5 to be the same number 0.5i want the numer 0.75 to be 1how can i do it i tried to use the round function but it is useless cause its rounding the 0.5 to 1 please help meand its round 0.25 to 0please help

#1
December 25, 2009 at 07:47:58
 The ROUND function isn't "useless"...=IF(A1=0.5,0.5,ROUND(A1,0))If A1 = .5, you'll get .5 otherwise the ROUND() function does what it's supposed to do.Hope that helps.

Report •

#2
December 25, 2009 at 09:33:30
 iam really sorry , but it didnot helpi used this function beforethe problem is the number can be 6.5 or 7.5 or 99.5 for example not only 0.5 what must i do it can be for example 8.5 not 0.5 or any number i mean its student reports it can be any (mark.5) (X.5)=(X.5)please help me.thank you:'(

Report •

#3
December 25, 2009 at 10:03:07
 I THINK WE DONT NEED ROUND FUNCTION MAYBE JUST IF FUNCTION CAUSE I THINKIF ( A1= X.5 IT WILL BE X.5 , IF (A1 = X.25 IT WILL RETURN X.25+0.25) SO X.25 WILL B2 X.5 AND I NEED X.75 TO BE ROUNDED ONLY.THANK YOU THIS IS MY MAIN PROBLEPLEASE HELP ME>

Report •

Related Solutions

#4
December 25, 2009 at 10:21:03
 Hi,Please don't shout. Everyone who responds is volunteering and I guess that most enjoy solving issues with Excel etc.If your value is in A1 use this formula:`=INT(A1)+IF(MOD(A1,1)<0.25,0,IF(MOD(A1,1)>0.75,1,0.5))`You can adjust the exact break points, as required, either changing values or using >=0.75 etc.Regards

Report •

#5
December 25, 2009 at 10:35:06
 IAM SORRY, BUT REALLY I DIDNOT SHOUT , I JUST WAS CRYINGTHE FORMULA ACTUALLY DIDNOT WORKCAUSEWHEN I TYPE 7.25 IT WILL RETURN 7.5 AND THIS IS TRUE7.5 IT WILL RETURN 7.5 AND THIS IS TRUE7.75 IT WILL RETURN 7.5 AND THIS FALSE BECAUSE IT MUST RETURN 8IAM SORRY AGAIN I REALLY NEED HELP

Report •

#6
December 25, 2009 at 10:48:40
 Hi,Using upper case letters is considered to be shouting - turn off your caps lock.The formula works, just adjust the break points.change >0.75 to >=0.75Regards

Report •

#7
December 25, 2009 at 10:51:05
 :) HI IT WORKED=INT(K10)+IF(MOD(K10;1)<0.25;0;IF(MOD(K10;1)>=0.7;1;0.5)) I REMOVED FROM 0.75 THE 5 I JUST PUT 0.7 AND IT WORKEDTHANK YOU VERY MUCHBUT MY BROPLEM DIDNOT SOLVED YET CAUSE I MUST MERGE THIS FUNCTION WITH THE VLOOKUP FUNCTIONJUST WAIT I WILL MERGIT THEN I WILL COME BACK THANK YOU AGAINI WISH LUCK FOR YOUREGARDS:')

Report •

#8
December 25, 2009 at 10:58:04
 Iam sorry about the capslock can i ask you one more question can i merge this function =VLOOKUP(\$L9;M.DaTa;10;FALSE) with the IF function whick you giveit to mecan you tell me how step by stepthank you for your help

Report •

#9
December 25, 2009 at 11:06:00
 Hi,I need a bit more information.1. Is M.DaTa a named range2. What does L9 contain.3. Do you want the 'rounding' function to be used in place of L9Regards

Report •

#10
December 25, 2009 at 11:15:10
 Hi L9 is a list of students numbers, i will give you an example student number student name mark 1 maram 99.25and M.Data is my table which contains the student number, student name and marksactually it a student report program thank you 2 humar 99.75so i make a list of student number when i select number 2 it will return for me your name and your mark

Report •

#11
December 25, 2009 at 11:40:34
 Hi,Here is an example that you can modify to fit your exact data.The sample data is in cells H4 to J6The student number is in column H, the names are in column I and the results (not rounded) are in column J``` H I J 4 1 maram 99.25 5 2 humar 99.75 6 3 doe 88.6 ```In Cell A1 enter the student number.In cell A2 enter this formula: =VLOOKUP(\$A\$1,\$H\$4:\$J\$6,2, FALSE)In cell B2 enter this formula: =INT(VLOOKUP(\$A\$1,\$H\$4:\$J\$6,3,FALSE))+IF(MOD(VLOOKUP(\$A\$1,\$H\$4:\$J\$6,3),1)<0.25,0,IF(MOD(VLOOKUP(\$A\$1,\$H\$4:\$J\$6,3),1)>=0.75,1,0.5))The VLOOKUP function looks for the student ID in cell A1 in the first column of the data in \$H\$4:\$J\$6In cell A2 it just returns the student's name from the second column (column I)In cell B2, the rounding formula uses the students score in three places in the formula, so VLOOKUP is used three times to return the score from the third column (column J).In the rounding part of the formula, I think that you should use >=0.75, for accuracy, rather than >=0.7, otherwise a score of X.7 will be rounded up, rather than returning X.5As you enter different numbers in cell A1 you will get each students name and rounded results in A2 and B2.Entering 3 in A1 gives the following:``` A B 1 3 2 doe 88.5 ```Regards

Report •

#12
December 25, 2009 at 11:49:42
 Thank you very muchi really dont know what to sayi will not forget your help everi will try my best , i will try the formula which you give it to me now and i will come back to you again to tell you the resulti hope it will work with methank you very muchregards,

Report •

#13
December 25, 2009 at 12:06:20
 Hi,Glad to help.If you need more help please ask - but I probably won't be on-line again today (Christmas day!)Best wishesHumar

Report •

#14
December 25, 2009 at 12:12:41
 hi please dont go i will tell what did i learn from yousee i have mergit=INT(VLOOKUP(\$L9;M.DaTa;10;FALSE))+IF(MOD(VLOOKUP(\$L9;M.DaTa;10;FALSE);1)<0.25;0;IF(MOD(VLOOKUP(\$L9;M.DaTa;10;FALSE);1)>=0.75;1;0.5))it worked with methank you very muchi just want to ask you if i the student bring 77.6 for example it will return 77.5 with this formulaand i needed to be 78 so can i change >= 0.75 to >=0.6 is that ok cause you tell me before to not put 0.7 so is this oki hope you are still onlineand happy Christmas for you :)thank you again very muchwaiting for your replyregards,

Report •

#15
December 25, 2009 at 12:32:13
 Hi,Breaks at <0.25 and >=0.75 gives you three bands decimals .0 to .24 all round down decimals .25 to .74 round to 0.5 decimals .75 to .99 all round upIt is your decision what each decimal rounds to.If you want .6 to round up you can use >=0.6but then you probably want 0.39 to round down using <0.4 instead of <0.25Try writing a table of scores 1.00 to 1.99 and then in the next column enter what you want each value to be. Then it is easier to decide what values to use instead of <0.25 and >=0.75Hope this helps.Regards

Report •

#16
December 25, 2009 at 12:44:40
 hiThank you very muchi will try and i will do what you told meiam really sorry for Destributing youi will tell you my result thank you very much againregards

Report •

#17
December 25, 2009 at 13:21:10
 Hi Mr. Humar, I really want to thank you for all your help.I tried all what did you ask me to do, and its all working with me.I really want to thank you for all your help. I will publish my program to my job on sunday and i will tell you the result for all your hard work.thank you againi really do appreciate your help,i really want to ask you one favor,i really hope that you can help me again and to teach me.sorry for destributing youthank you againbest wishes for youregards,Maram

Report •

#18
December 26, 2009 at 02:12:24
 Hi Mr. HumarI hope that you are online.Iam facing a problemfor the same formula if i want to put the range <0.04 and >= 0.55 its not workingi need 99.04 to be 99 only99.04 to be 9999.05 to be 99.599.5 to be 99.599.54 to be 99.599.55 to be 100please i need helpregards,

Report •

#19
December 26, 2009 at 05:13:56
 Hi,The reason 99.04 is not returning 99, is because you used <0.04.04 is not less than .04 and therefore the formula returns 99.5Use <=0.4 and then .44 and smaller decimal values will round down to zero. The formula tests values according to the operator such as < or = . It may help to talk through the formula. With your original <0.4 the formula was saying if 0.4 is less than 0.4 then return zero but if 0.4 is not less than 0.4 then if 0.4 is greater than or equal to 0.55 return 1, but if 0.4 is not equal to or greater than 0.55 then return 0.5 (which is what it was doing).With <=0.4 you get this: if 0.4 is less than or equal to 0.4 then return zero (which is what it will do and it meets your requirements).Regards

Report •

#20
December 26, 2009 at 05:51:53
 Hi Mr. Humarthe problem is in another way, I meanmy manager asked me whenever the student bring X.05 and above it must return X.5and i mention the 0 before the 5 because when its round it will be x.1 right mr. humarand she want x.5 to be x.5and she want x.55 and above to return full mark so if x was 6.55 it must return then 6 i feel that this is un fair for students because they will get more marksbut i must find the right formula as she askedplease help mei feel it really complicatedregards ,

Report •

#21
December 26, 2009 at 07:04:57
 Hi,Use <=0.04 and >=0.55I have used <=0.04 rather than <0.05, because the Mod function is not entirely accurate for some values.I assume that scores are never given to more than two decimal places.Regards

Report •

#22
December 26, 2009 at 16:59:11
 Hi I used these ranges before when i type 80.05 it gives me 80.5 and this is true but when i when i type 80.04 it gives me also 80.5 and this is wrong it must give me 80 only , i dont why its not working , please can you tryit , i think this formula only works until the 20.04 0r 30.04 only.please help me,regards

Report •

#23
December 27, 2009 at 08:37:39
 Hi,The problem is occurring because the MOD function has variations in some of the decimal places, and does not return exact values.You can get over this problem by rounding the result of the MOD calculations as follows:=INT(VLOOKUP(\$L9,N9:O12,2,FALSE))+IF(ROUND(MOD(VLOOKUP(\$L9,N9:O12,2,FALSE),1),3)<0.05,0,IF(ROUND(MOD(VLOOKUP(\$L9,N9:O12,2,FALSE),1),3)>=0.55,1,0.5))Regards

Report •

#24
December 27, 2009 at 09:48:11
 Hi,If a formula does not return the expected value try stepping through it using Tools - Formula Auditing - Evaluate Formula.This allows you to see the results of each part of a formula, and in this case showed why the result was not as expected.Regards

Report •

#25
December 28, 2009 at 05:12:23
 Hi Mr. Humar, I really want to thank you for every thing, I want to thank you for helping me, really you have save me from a big trouble, I really dont know what to say. you are a genius teacher, I really want to be like you in excel, i hope that you can help me always in excel. i will tell you my program result after a week and i know that it will be alright and i will show you the hard work for your formula, i hope that the program will be fine cause iam programing before 4 month until now without sleeping and its still need developing, i hope that you can help me, thank you for your help you are really an excellant teacher,thank you againregards,maram

Report •

#26
December 28, 2009 at 06:19:19
 re: i am programing before 4 month until now without sleepingIf I had been programming for 4 months without sleeping, I'd be having trouble figuring things out also.Get some rest!

Report •

#27
December 28, 2009 at 07:18:17
 Thank you Mr. Derby I will try its not easy when you have a big job, am really tired to death. thank you , i hope that i can really sleep like other people, but its difficult.and you are right we people need rest to get power to fix things out.but sometimes we dont have the time to rest, but when you are an I.T specialist ( techinical support ) & multi serve for technology and graphic designer you will wish the day to be more than 48 hours.iam sorry , but iam really tired.thank youregrads,

Report •