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

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.

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

:'(

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 PROBLE

PLEASE HELP ME>

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

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 8IAM SORRY AGAIN I REALLY NEED HELP

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

:) 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

:')

Iam sorry about the capslockcan 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

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 L9Regards

HiL9 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

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 JH I J 4 1 maram 99.25 5 2 humar 99.75 6 3 doe 88.6In 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.5Regards

Thank you very muchi 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,

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

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 onlineand happy Christmas for you :)

thank you again very much

waiting for your reply

regards,

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

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

Hope this helps.

Regards

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

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 youregards,

Maram

Hi Mr. Humar I hope that you are online.

Iam facing a problem

for the same formulaif i want to put the range <0.04 and >= 0.55

its not workingi 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 100please i need help

regards,

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

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. humarand 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 askedplease help me

i feel it really complicated

regards ,

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

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

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

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

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

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!

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,

Ask Your Question

Weekly Poll

Do you think Google should sell budget phones in the US?

Discuss in The Lounge

Poll History