Problem Creating a Formula

just_jon

Legend

Joined

Sep 3, 2002

Messages

10,473

• Jan 13, 2005

• #2

Hi, hello and welcome to MrExcel -- try -

=IF(A1>80,"Over 80",IF(A1>=63,22,IF(A1>=40,14,IF(A1>=0,0,"Less than zero"))))

Where A1 is the cell you want checked.

If it cannot be less than zero, then -

=IF(A1>80,"Over 80",IF(A1>=63,22,IF(A1>=40,14,0)))

EDIT - your ranges overlap - 40-63 and 63-80, ex. Assumed it was 0-39.99=0,40-63=14,63.01-80=22

Post back if something else.

 

Upvote 0

W

WHYME

New Member

Joined

Jan 12, 2005

Messages

10

• Jan 13, 2005

• #3

Try

=IF(A1>=0, 0)+ IF(A1>=40, 14)+ IF(A1>=63, 22)

Should work

 

Upvote 0

just_jon

Legend

Joined

Sep 3, 2002

Messages

10,473

• Jan 13, 2005

• #4

WHYME said:

Try

=IF(A1>=0, 0)+ IF(A1>=40, 14)+ IF(A1>=63, 22)

Should work

Click to expand...

When evaluating conditions, you must either evaluate from high to low, or provide both upper and lower limits.

As both 40 and 63 are >=0, the above will return 0 if A1 is positive.

 

Upvote 0

T

tiveytim

New Member

Joined

Jan 13, 2005

Messages

22

• Jan 13, 2005

• #5

Wow

Many Thanks for that one. The first formula was brilliant.

I do have another formula i am having trouble with. I am going to give it another go and if not i will reply with it and see if you can help.

Many thanks again...

 

Upvote 0

T

tiveytim

New Member

Joined

Jan 13, 2005

Messages

22

• Jan 13, 2005

• #6

Re: Problem Creating a Formula - Problem 2

Hi Again

Problem formula 2 is a lot harder:

I have boxes as below:

Top Bottom Price
12 4 £0
12 12 £6
14 12 £53
24 24 £58
28 12 £64
56 24 £125

the cell with the formula needs to show the price of the box required. e.g if they have 12 at the top and 8 bottom then the box is £6. If they had 24 top and 24 bottom the the price would be 125 as you need the bigger bottom box.

Has anyone any ideas for this. I imagine it is a long formula. If you need more info then let me know.

Many thanks in Advance!!!

 

Upvote 0

just_jon

Legend

Joined

Sep 3, 2002

Messages

10,473

• Jan 13, 2005

• #7

Guessing at -

Book1
	A	B	C	D
1	Top	Bottom	Price
2	12	4	0
3	12	12	6
4	14	12	53
5	24	24	58
6	28	12	64
7	56	24	125
8
9	24	24	58
10
Sheet3

C9: =INDEX(C1:C7,MAX(MATCH(A9,A1:A7,0),MATCH(B9,B1:B7,0)),0)

 

Upvote 0

T

tiveytim

New Member

Joined

Jan 13, 2005

Messages

22

• Jan 13, 2005

• #8

Many thanks

So am i right in saying if i want to give more options then i just extend the match coloums?

Thanks again

 

Upvote 0

just_jon

Legend

Joined

Sep 3, 2002

Messages

10,473

• Jan 13, 2005

• #9

Yes, just keep on going down and adjust the formula references accordingly.

They can, in fact, be whole column references == C:C, etc

 

Upvote 0