If Function (or maybe something else)

[jazminfig]

I am trying to find an equation that looks at my data, lets say column A, and if it falls between 1-10 then subtract by 10, but if the same column falls between 11-19 then subtract by 5, and so for. Please help, I have read various examples and watched various videos but still can not figure this out.

 

[NARAYANK991]

Hi ,

When you say column A , I assume you wish to go cell by cell.

Let us assume your data is in the range A2:A200 ; in cell B2 , assuming column B is an unused column , put in the following formula :

=IF(A2 <=10 , A2 - 10 , IF(A2 <= 19 , A2 - 5 , "Not mentioned"))

What this will do is if A2 has any value less than or equal to 10 , it will subtract 10 from that value and put it in cell B2.

If A2 has any value between 11 and 19 , it will subtract 5 from that value and put it in cell B2.

Thus for all values in A2 which are positive , but between 0 and 9 , the result will be a negative value in B2.

Is this what you want ? If yes , then copy the above formula to the remaining cells B3 through B200.

 

[jazminfig]

This did not work, it returns a false data. Here is some of the actual data and it starts at I2.
I tried =if(I2<=315,I2-269,if(I2<=329,I2-316,if(I2<=345,I2-330,If(I2<=359,I2-346,If(I2<=391,I2-360) after i press enter all data returns as false.

Ranges I actually want
269 - 315 (subtract 269)
316 - 329 (subtract 316)
330 - 345 (subtract 330)
346-359 (subtract 346)
360 - 391 (subtract 360)

<style type="text/css"><!--br {mso-data-placement:same-cell;}--></style>

260
265
265
266
278
285
291
293
298
301
302
314
320
337

<colgroup><col style="width: 89px"></colgroup><tbody>
</tbody>

Hi ,

When you say column A , I assume you wish to go cell by cell.

Let us assume your data is in the range A2:A200 ; in cell B2 , assuming column B is an unused column , put in the following formula :

=IF(A2 <=10 , A2 - 10 , IF(A2 <= 19 , A2 - 5 , "Not mentioned"))

What this will do is if A2 has any value less than or equal to 10 , it will subtract 10 from that value and put it in cell B2.

If A2 has any value between 11 and 19 , it will subtract 5 from that value and put it in cell B2.

Thus for all values in A2 which are positive , but between 0 and 9 , the result will be a negative value in B2.

Is this what you want ? If yes , then copy the above formula to the remaining cells B3 through B200.

 

[FDibbins]

If you want this to happen in the same cell that the data is in, you will need VBA (code) for that, a formula wont do it.

I would do this with a small lookup table...

	A​	B​	C​	D​	E​
2​	290​	21​		269​	269​
3​	300​	31​		315​	316​
4​	310​	41​		329​	330​
5​	320​	4​		345​	346​
6​	330​	0​		359​	360​
7​	340​	10​
8​	350​	4​
9​	360​	0​
10​	370​	10​
11​	380​	20​
12​	390​	30​
13​	400​	40​

and E would be the lookup table
B2=A2-VLOOKUP(A2,$D$2:$E$6,2,1)
copied down

 

[jazminfig]

It Worked!! The original formula actually worked. I was using google sheets and once I downloaded it as an excel document it showed me that the numbers I was working with were actually stored as text, as appose to numbers. This issue caused the equation to not register it. Once I fixed the issue then the equation registered and I was able to work it out. Thank you for your help!

This did not work, it returns a false data. Here is some of the actual data and it starts at I2.
I tried =if(I2<=315,I2-269,if(I2<=329,I2-316,if(I2<=345,I2-330,If(I2<=359,I2-346,If(I2<=391,I2-360) after i press enter all data returns as false.

Ranges I actually want
269 - 315 (subtract 269)
316 - 329 (subtract 316)
330 - 345 (subtract 330)
346-359 (subtract 346)
360 - 391 (subtract 360)

<style type="text/css"><!--br {mso-data-placement:same-cell;}--></style>

260
265
265
266
278
285
291
293
298
301
302
314
320
337

<tbody>
</tbody>

 

[FDibbins]

Maybe consider using the more efficient (and easier to edit/add) formula I suggested in post #4 ?

 

[jazminfig]

I have never done a vba code so didn't try because the initial equation worked.

Maybe consider using the more efficient (and easier to edit/add) formula I suggested in post #4 ?

 

[FDibbins]

My suggestion was not VBA (which I am really weak at, btw), it used a regular formula and a small table