If Statement Otherwise VBA

[heartyy]

I been trying to make this formula to work but I can't seem to make it work. The formula is on cell B1, =IF(AND(A1>200,A1<300),A1-200, otherwise do nothing). What I want for this to happen is if A1 is greater than 300 is to do nothing and the value that's already on B1 is not affected. If VBA is needed since it can not have a value in cell B1 then can you create me a VBA statement please. Much appreciated. Thank you.

 

[rlv01]

If B1 contains a formula

tmp777.xlsm
	A	B
1	150	151
2	250	251
3	350	351
Sheet12

Cell Formulas
Range	Formula
B1:B3	B1	=A1+1

you can just incorporate it into the IF statement:

tmp777.xlsm
	A	B
1	150	151
2	250	50
3	350	351
Sheet12

Cell Formulas
Range	Formula
B1:B3	B1	=IF(AND(A1>200,A1<300),A1-200, A1+1)

 

[heartyy]

This doesn't work because if cell A1 is less than 200 then cell B1 should be 0. Now, if cell A1 is 299 then the value in cell B1 should be 99 because it's over 200 but if cell A1 is greater then 300 then cell B1 should remain 99 and not change the value in that cell. Can this be done? If VBA is needed please show me the statment for it. Thank you.

 

[rlv01]

This doesn't work because if cell A1 is less than 200 then cell B1 should be 0. Now, if cell A1 is 299 then the value in cell B1 should be 99 because it's over 200 but if cell A1 is greater then 300 then cell B1 should remain 99 and not change the value in that cell. Can this be done? If VBA is needed please show me the statment for it. Thank you.

You have only provided a very small amount of information. Unless you can better define the formula, it will be difficult to help you.
Reading through your posts, here is my guess about what you want.

If A1 > 200 AND A1 < 300 then B1 = A1 - 200
If A1 <= 200 then B1 = 0
If A1 >= 300 then B1 = 99

That seems doable via a formula. No need for VBA

tmp777.xlsm
	A	B
1	0	0
2	100	0
3	199	0
4	200	0
5	201	1
6	202	2
7	250	50
8	298	98
9	299	99
10	300	99
11	301	99
12	9999	99
Sheet12

Cell Formulas
Range	Formula
B1:B12	B1	=IF(A1<=200,0,IF(A1>=300,99,A1-200))

If I have guessed wrong, please explain with more detailed information.

 

[heartyy]

Sorry, I should have check it carefully. Your formula =IF(A1<=200,0,IF(A1>300,99,A1-200)) did not work for greater 300. The 99 in red it should be varable number meaning any number and not 99. Here's an example I created by month.

A B C
Jan. 285 85

Feb. 305 85

1. For January cell B is greater than 200 but less than 300 and cell C shows how much it's over 200.

2. For February cell B is greater than 300 therefore for February cell C number you use your last number that is greater than 200 but less than 300 for February cell C. In this case it was last month which is 85.

Bottomline is that you don't report any number that is greater than 300 for cell C. It's always greater than 200 but less than 300 for cell C so in this case we use the same number from January cell C which is 85 for February cell C.

I'm not very good explaining things but I hope you have a pretty good idea what I'm getting at. Thank you.

Oh, =IF(A1 <=200,0. This is good.

 

[rlv01]

I hope you have a pretty good idea what I'm getting at. Thank you.

I am sorry, but I do not. At all. Your most recent post has increased my confusion by 1000%. Perhaps someone else will come along to help. You might consider upgrading your abilty to post good examples by using this free tool which many here on the MrExcel board use:

XL2BB - Excel Range to BBCode

Excel 'mini-sheet' in messages - XL2BB Although experts prefer to read your description and question instead of working in your actual file to solve your problem, there are times that it is difficult to explain an issue without providing actual...

www.mrexcel.com