Logical , functions , on ( IF , AND , OR)

**The Learner** · 11-20-2012, 02:56 AM

Good Day, Hope somebody can assist me with this logical and mindboggling functions.
I am trying to group a rating on point system say for instance between 55 - 65 ( 5 points ) , 66 - 76 ( 10 points ) 77 -87 ( 15 points ) 88 - 98 ( 20 points ) ansd so forth.

the Formula that i tried to use is in D1 whiich is =IF(AND(B2>113,B2>103),"25",IF(AND(B2>102,B2>92),"20",IF(AND(B2>91,B2>81),"15",IF(AND(B2>80,B2>70),"10",IF(AND(B2>69,B2>59),"5",IF(AND(B2>55,B2>65),"5"))))))

In (B2: B6) is the ratings , 100, 95, 75, 69, 65, 88.

The issue is the points returned in D1: D6 looks incorreect as 15 , 5 , 5 , False , 10 , False .

From my understading it should have returned 20 , 20 , 10 , 5 , 5 , 10 if i correct.

**dogberry** · 11-20-2012, 03:05 AM

Hi

The formula for D6 is referencing B7 an empty cell.

Chris

**Fotis1991** · 11-20-2012, 03:11 AM

In D1 and copy down.

IF(AND(B1<=113,B1>=103),25,IF(AND(B1<=102,B1>=92),20,IF(AND(B1<=91,B1>=81),15,IF(AND(B1<=80,B1>=70),10,IF(AND(B1<=69,B1>=59),5,IF(AND(B1>=55,B1<=65),5,""))))))

**The Learner** · 11-20-2012, 03:21 AM

Thanks, Brilliant
I think i should hang out more in this forum.

**Fotis1991** · 11-20-2012, 03:27 AM

You are welcome.

YES. If you like Excel you should hang out more in this forum. You ;ll have to learn a lot!

Thanks for the reb* and for your kind comments!

**vlady** · 11-20-2012, 03:29 AM

another i think which follows below statement of your's.

I am trying to group a rating on point system say for instance between 55 - 65 ( 5 points ) , 66 - 76 ( 10 points ) 77 -87 ( 15 points ) 88 - 98 ( 20 points ) ansd so forth.

=LOOKUP(B1,{0,55,66,77,88,99},{0,5,10,15,20,25})

**crzu** · 11-20-2012, 03:35 AM

Originally Posted by The Learner

55 - 65 ( 5 points ) , 66 - 76 ( 10 points ) 77 -87 ( 15 points ) 88 - 98 ( 20 points )

Base on your request, it is still short of some information.

For score below 55, will it be 0 points or 5 points? For score above of 98, will it be 20 points or higher?

Below is the formula I used.

Formula:

=IF($B1-54<=0,0,ROUNDUP(($B1-54)/11,0)*5)

Attached is your file.

**Pepe Le Mokko** · 11-20-2012, 03:41 AM

Thois one also seems to work

=(FLOOR((B1-55)/11,1)+1)*5*(B1>55)

**crzu** · 11-20-2012, 03:48 AM

Originally Posted by Pepe Le Mokko

Thois one also seems to work

=(FLOOR((B1-55)/11,1)+1)*5*(B1>55)

It return "#NUM!" for number lesser than 55.

**Pepe Le Mokko** · 11-20-2012, 04:30 AM

Originally Posted by crzu

It return "#NUM!" for number lesser than 55.

Returns 0 for me

Logical , functions , on ( IF , AND , OR)

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Logical , functions , on ( IF , AND , OR)

Re: Logical , functions , on ( IF , AND , OR)

Re: Logical , functions , on ( IF , AND , OR)

Re: Logical , functions , on ( IF , AND , OR)

Re: Logical , functions , on ( IF , AND , OR)

Re: Logical , functions , on ( IF , AND , OR)

Re: Logical , functions , on ( IF , AND , OR)

Re: Logical , functions , on ( IF , AND , OR)

Re: Logical , functions , on ( IF , AND , OR)

Re: Logical , functions , on ( IF , AND , OR)

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions