If Function (or maybe something else)

J

jazminfig

New Member
Joined
Nov 2, 2017
Messages
4
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. :confused::confused::confused::mad:


 

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.
Sort by date Sort by votes
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.
 
Last edited:
Upvote 0
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>[TABLE="width: 0"]
<colgroup><col style="width: 89px"></colgroup><tbody>[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=B8CCE4]#B8CCE4[/URL] , align: center"]260[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DBE5F1]#DBE5F1[/URL] , align: center"]265[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=B8CCE4]#B8CCE4[/URL] , align: center"]265[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DBE5F1]#DBE5F1[/URL] , align: center"]266[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=B8CCE4]#B8CCE4[/URL] , align: center"]278[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DBE5F1]#DBE5F1[/URL] , align: center"]285[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=B8CCE4]#B8CCE4[/URL] , align: center"]291[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DBE5F1]#DBE5F1[/URL] , align: center"]293[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=B8CCE4]#B8CCE4[/URL] , align: center"]298[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DBE5F1]#DBE5F1[/URL] , align: center"]301[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=B8CCE4]#B8CCE4[/URL] , align: center"]302[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DBE5F1]#DBE5F1[/URL] , align: center"]314[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=B8CCE4]#B8CCE4[/URL] , align: center"]320[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DBE5F1]#DBE5F1[/URL] , align: center"]337[/TD]
[/TR]
</tbody>[/TABLE]


NARAYANK991 said:
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.
Click to expand...
 
Upvote 0
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...
[Table="width:, class:grid"][tr][td] [/td][td]
A​
[/td][td]
B​
[/td][td]
C​
[/td][td]
D​
[/td][td]
E​
[/td][/tr]
[tr][td]
2​
[/td][td]
290​
[/td][td]
21​
[/td][td][/td][td]
269​
[/td][td]
269​
[/td][/tr]

[tr][td]
3​
[/td][td]
300​
[/td][td]
31​
[/td][td][/td][td]
315​
[/td][td]
316​
[/td][/tr]

[tr][td]
4​
[/td][td]
310​
[/td][td]
41​
[/td][td][/td][td]
329​
[/td][td]
330​
[/td][/tr]

[tr][td]
5​
[/td][td]
320​
[/td][td]
4​
[/td][td][/td][td]
345​
[/td][td]
346​
[/td][/tr]

[tr][td]
6​
[/td][td]
330​
[/td][td]
0​
[/td][td][/td][td]
359​
[/td][td]
360​
[/td][/tr]

[tr][td]
7​
[/td][td]
340​
[/td][td]
10​
[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]
8​
[/td][td]
350​
[/td][td]
4​
[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]
9​
[/td][td]
360​
[/td][td]
0​
[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]
10​
[/td][td]
370​
[/td][td]
10​
[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]
11​
[/td][td]
380​
[/td][td]
20​
[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]
12​
[/td][td]
390​
[/td][td]
30​
[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]
13​
[/td][td]
400​
[/td][td]
40​
[/td][td][/td][td][/td][td][/td][/tr]
[/table]
and E would be the lookup table
B2=A2-VLOOKUP(A2,$D$2:$E$6,2,1)
copied down
 
Upvote 0
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!

jazminfig said:
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>[TABLE="width: 0"]
<tbody>[TR]
[TD="align: center"]260[/TD]
[/TR]
[TR]
[TD="align: center"]265[/TD]
[/TR]
[TR]
[TD="align: center"]265[/TD]
[/TR]
[TR]
[TD="align: center"]266[/TD]
[/TR]
[TR]
[TD="align: center"]278[/TD]
[/TR]
[TR]
[TD="align: center"]285[/TD]
[/TR]
[TR]
[TD="align: center"]291[/TD]
[/TR]
[TR]
[TD="align: center"]293[/TD]
[/TR]
[TR]
[TD="align: center"]298[/TD]
[/TR]
[TR]
[TD="align: center"]301[/TD]
[/TR]
[TR]
[TD="align: center"]302[/TD]
[/TR]
[TR]
[TD="align: center"]314[/TD]
[/TR]
[TR]
[TD="align: center"]320[/TD]
[/TR]
[TR]
[TD="align: center"]337[/TD]
[/TR]
</tbody>[/TABLE]
Click to expand...
 
Upvote 0
Maybe consider using the more efficient (and easier to edit/add) formula I suggested in post #4 ?
 
Upvote 0
My suggestion was not VBA (which I am really weak at, btw), it used a regular formula and a small table :)
 
Upvote 0
You must log in or register to reply here.

Similar threads

G
  • Solved
If Then else in Excel
Replies
9
Views
361
Gulfcourse
G
B
  • Question Question
Excel Table Lookup with OR Conditions and Multiple Columns
Replies
1
Views
158
BenW71
B
R
  • Question Question
How can I get the value of a specific column in a range.
Replies
4
Views
549
Scott Huish
Scott Huish
J
  • Question Question
Simple Excel Formulas Not Showing Correct Answer (shows Dash or 0 depending on formatting) NOT a "Numbers Stored As Text" issue!
Replies
6
Views
1K
Alex Blakenburg
A
D
  • Solved
VBA Conditional Statement That Functions Based on Today's Date
2
Replies
18
Views
829
Cubist
Cubist

Forum statistics

Threads
1,224,823
Messages
6,181,184
Members
453,020
Latest member
Mohamed Magdi Tawfiq Emam

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top