Help with IF formula

[WillM66]

Hello,

My partner has asked me to help with a spreadsheet she uses in her role as a Teaching Assistant. The spreadsheet is used to calculate 'points' for good work/behaviour which are then converted to a cash value (which can be redeemed for various awards/prizes).

The formula they are using is as follows:

=(IF(AND(D51>=0,D51<=25),0,IF(AND(D51>=26,D51<=30),1,IF(AND(D51>=31,D51<=34),3,IF(AND(D51>=35,D51<=100),5,"")))))+E51+F51+G51

D51 is the total points awarded for a student in that week.

What they have, however, is 2 groups of students where the parameters are different. These are 'TEC1' and 'TEC2' (these are listed in Column C in the spreadsheet). They will be awarded £1 if they get between 30-35 points and £3 if they get between 35-39 points.

Essentailly she needs a formala which says IF its TEC1 or TEC2 the rules are x , if not the rules are y.

Thanks in advance.

Will M

 

[Fluff]

What are the rules for TEC1 & TEC2?

 

[hernantorres23]

Hi, but this formula

The formula they are using is as follows:

=(IF(AND(D51>=0,D51<=25),0,IF(AND(D51>=26,D51<=30),1,IF(AND(D51>=31,D51<=34),3,IF(AND(D51>=35,D51<=100),5,"")))))+E51+F51+G51

They will be awarded £1 if they get between 30-35 points and £3 if they get between 35-39 points.

Will M

does not include this range

They will be awarded £1 if they get between 30-35 points and £3 if they get between 35-39 points.

 

[hernantorres23]

...

Well, following your example...
In addition, you should check the condition 30-35 points and £3 if they get between 35-39 points, because point 35 must be part of a single comparison vector

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1
2
3			Type	Week points				Suggestion
4			TEC1	39				5
5			TEC2	39				3
6
7										LEVEL	TEC1	Money		LEVEL	TEC2	Money
8										1	0-25	0		1	0-29	0
9										2	26-30	1		2	30-34	1
10										3	31-34	3		3	35-39	3
11										4	35-100	5		4	40-100	5
12
Sheet14

Cell Formulas
Range	Formula
H4:H5	H4	=IF(C4="TEC1",IF(AND(D4>=0,D4<=25),0,IF(AND(D4>=26,D4<=30),1,IF(AND(D4>=31,D4<=34),3,IF(AND(D4>=35,D4<=100),5,""))))+E4+F4+G4,IF(AND(D4>=0,D4<=29),0,IF(AND(D4>=30,D4<=34),1,IF(AND(D4>=35,D4<=39),3,IF(AND(D4>=40,D4<=100),5,""))))+E4+F4+G4)

Cells with Data Validation
Cell	Allow	Criteria
C4:C5	List	TEC1,TEC2

 

[Fluff]

Another option
=IF(C51="Tec1",LOOKUP(D51,{0,26,31,35},{0,1,3,5}),IF(C51="Tec2",LOOKUP(C51,{0,30,36,40},{0,1,3,5}),""))+SUM(E51:G51)

 

[WillM66]

Hello,

I was probably unclear in my original post.

The majority of classes have the following parameters:

Between 26 and 31 points = £1
Between 31 and 35 points = £3

These classes have a variety of names and are all found in Column C.

Only classes TEC1 and TEC2 have different parameters as follows:

Between 30 and 35 points = £1
Between 35 and 39 points = £3

I want the formula to say, if it's TEC 1 or TEC 2 award x points, if it's anything other than TEC1 or TEC2 award y points.

Hope this makes more sense.

Will M

 

[Fluff]

Ok, how about
=IF(OR(C51={"Tec1","Tec2"}),LOOKUP(D51,{0,30,36,40},{0,1,3,5}),LOOKUP(D51,{0,26,31,35},{0,1,3,5}))+SUM(E51:G51)

 

[hernantorres23]

Hi.

...
The majority of classes have the following parameters:

Between 26 and 31 points = £1
Between 31 and 35 points = £3

Tell me, if someone gets 31 points, how many pounds would earn? £1 or £3?

Well, same for this case

...
Only classes TEC1 and TEC2 have different parameters as follows:

Between 30 and 35 points = £1
Between 35 and 39 points = £3
...

Tell me, if someone gets 35 points, how many pounds would earn? £1 or £3?

For better experience, If you want...
Please try prepare a data example using add-in specifically for this and it can be found here XL2BB
Pay attention to this post XL2BB 2 Squares

 

[WillM66]

Update:

Hello, i ended up doing a two stage approach using an IFS formula, the output of which is then used in a VLOOKUP. All is working swimmingly, but we have hit a problem with ecel versions. Looks like the IFS doens't work on an early version of excel that they're using in the school. So what I need to do is convert this formula: =IFS(B3="TEC1",3,B3="TEC2",3,B3<>"tec1",2,B3<>"tec2",2) To work just using IF. I've tried following some guidance on nested/multiple IF formula, but I'm getting nowhere - any helpo ? thanks, Will M

 

[hernantorres23]

Maybe
=--LEFT(IF(B3="TEC1",3,"") & IF(B3="TEC2",3,"")&IF(B3<>"TEC1",2,"")& IF(B3<>"TEC2",2,""),1)

But I insist, that you should check your conditions