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

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
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
293
Gulfcourse
G
R
  • Question Question
How can I get the value of a specific column in a range.
Replies
4
Views
402
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
767
Cubist
Cubist
S
  • Question Question
Excel VBA - Sort & Get Totals - Logic
Replies
4
Views
333
STARL0RD
S

Forum statistics

Threads
1,223,164
Messages
6,170,444
Members
452,326
Latest member
johnshaji

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