Please help with excel ROUND function

Microsoft Excel 2007
December 25, 2009 at 05:27:10
Specs: Windows Vista
iam programming a student reports and i need help in this manner
my question is
i want the number 0.25 to be 0 only
i want the number 0.5 to be the same number 0.5
i want the numer 0.75 to be 1
how 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 me
and its round 0.25 to 0
please help

See More: Please help with excel ROUND function

Report •


#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 help
i used this function before
the 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 THINK

IF ( 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 PROBLE

PLEASE 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 CRYING

THE FORMULA ACTUALLY DIDNOT WORK
CAUSE
WHEN I TYPE
7.25 IT WILL RETURN 7.5 AND THIS IS TRUE
7.5 IT WILL RETURN 7.5 AND THIS IS TRUE
7.75 IT WILL RETURN 7.5 AND THIS FALSE BECAUSE IT MUST RETURN 8

IAM 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.75

Regards


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 WORKED

THANK YOU VERY MUCH

BUT MY BROPLEM DIDNOT SOLVED YET CAUSE I MUST MERGE THIS FUNCTION WITH THE VLOOKUP FUNCTION

JUST WAIT I WILL MERGIT THEN I WILL COME BACK

THANK YOU AGAIN

I WISH LUCK FOR YOU

REGARDS

:')


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 me

can you tell me how step by step

thank 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 range
2. What does L9 contain.
3. Do you want the 'rounding' function to be used in place of L9

Regards


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.25

and M.Data is my table which contains the student number, student name and marks

actually it a student report program

thank you


2 humar 99.75


so 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 J6
The 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$6
In 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.5

As 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 much

i really dont know what to say

i will not forget your help ever

i 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 result

i hope it will work with me
thank you very much
regards,


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 wishes

Humar


Report •

#14
December 25, 2009 at 12:12:41
hi

please dont go

i will tell what did i learn from you

see 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 me

thank you very much

i just want to ask you if i the student bring 77.6 for example it will return 77.5 with this formula
and 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 ok
i hope you are still online

and happy Christmas for you :)

thank you again very much

waiting for your reply

regards,


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 up

It is your decision what each decimal rounds to.

If you want .6 to round up you can use >=0.6
but then you probably want 0.39 to round down using <0.4 instead of <0.25

Try 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.75

Hope this helps.

Regards


Report •

#16
December 25, 2009 at 12:44:40
hi

Thank you very much

i will try and i will do what you told me

iam really sorry for Destributing you

i will tell you my result

thank you very much again

regards


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 again
i 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 you

thank you again
best wishes for you

regards,
Maram


Report •

#18
December 26, 2009 at 02:12:24
Hi Mr. Humar

I hope that you are online.

Iam facing a problem
for the same formula

if i want to put the range <0.04 and >= 0.55
its not working

i need 99.04 to be 99 only
99.04 to be 99
99.05 to be 99.5
99.5 to be 99.5
99.54 to be 99.5
99.55 to be 100

please i need help
regards,


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.5

Use <=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. Humar

the problem is in another way, I mean

my manager asked me whenever the student bring X.05 and above it must return X.5
and i mention the 0 before the 5 because when its round it will be x.1 right mr. humar

and she want x.5 to be x.5

and 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 marks
but i must find the right formula as she asked

please help me

i feel it really complicated

regards ,


Report •

#21
December 26, 2009 at 07:04:57
Hi,

Use <=0.04 and >=0.55

I 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 again
regards,
maram


Report •

#26
December 28, 2009 at 06:19:19
re: i am programing before 4 month until now without sleeping

If 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 you
regrads,


Report •


Ask Question