IF-THEN - expanding on the basic formula

[Malthus101]

Hi folks, long time no speak!


I have one cell which contains the account balance - let's say it's: $1111.

Another cell has a figure I need to reference. If that reference cell is EQUAL TO OR LESS than the account balance cell, I need to print: 1

If it's more, less than double or double it, I need to print 2.
If it's more, less than triple or double it, I need to print 3.

So the tricky part is, printing the correct value, and not too high of a number.

Examples:

Account is: $1111
Ref cell is: $250 - print 1
Ref cell is: $1111 -print 1
Ref cell is $1111.01 - $2222 - print 2
Ref cell is: $2222.01 - $3333 - print 3

What's the formula?

Thanks!

 

[earthworm]

try below

Book1
	A	B	C	D	E
1	1,111.00	Print 1		250.00	Print 1
2	250.00	Print 1		1,111.00	Print 1
3	1,111.00	Print 1		1,111.01	Print 2
4	1,111.01	Print 2
5	2,222.01	Print 2
Sheet1

Cell Formulas
Range	Formula
B1:B5	B1	=VLOOKUP(A1,$D$1:$E$4,2)

 

[Peter_SSs]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

See if this does what you want.

21 11 12.xlsm
	A	B	C
1	Bal	Ref	Result
2	1111	250	1
3		1111	1
4		1111.01	2
5		2222.01	3
Ref

Cell Formulas
Range	Formula
C2:C5	C2	=INT((B2-0.000001)/$A$2)+1

 

[Malthus101]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

See if this does what you want.

21 11 12.xlsm
	A	B	C
1	Bal	Ref	Result
2	1111	250	1
3		1111	1
4		1111.01	2
5		2222.01	3
Ref

Cell Formulas
Range	Formula
C2:C5	C2	=INT((B2-0.000001)/$A$2)+1

That's the one -thanks very much.

 

[Malthus101]

try below

Book1
	A	B	C	D	E
1	1,111.00	Print 1		250.00	Print 1
2	250.00	Print 1		1,111.00	Print 1
3	1,111.00	Print 1		1,111.01	Print 2
4	1,111.01	Print 2
5	2,222.01	Print 2
Sheet1

Cell Formulas
Range	Formula
B1:B5	B1	=VLOOKUP(A1,$D$1:$E$4,2)

Thanks for your help too, Earthworm.

 

[Peter_SSs]

That's the one -thanks very much.

You're welcome. Thanks for the follow-up.

Also thanks for updating your profile.