What does this do?

Otispunkmeyer

New Member
Joined
Oct 2, 2006
Messages
8
=IF(699>D2,$S$2,IF(799>D2,$S$3,IF(899>D2,$2$4)))

its actually longer, but it just keeps going ie 999>D2, 1099>D2, 1199>D2 etc

what does it do? the previous guy who worked on what im working on now has written this down as a method of sorting data, and as good as he has been writing it down, he hasnt explained what the hell it does.

does it find if numbers are bigger than 699 and less than 799 then it sums them up in a new box?
 

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
Looks like he is applying a grouping value to the rows.

If the value in column D is greater than 699, he is putting something like ">699" in the cell, if greater than 799, something like ">799", or whatever is in column S.
 
Upvote 0
Looks at the value in D2 and, if it is less than 699, returns the value in S2. If it is larger, then it looks at D2 and, if it is less than 799, returns the value in S3. If it is larger etc etc

As soon as it finds the correct equation, it stops.

You can copy it down and it will check the relative cell against the test (699, 799 etc) and always respond with the fixed cell beginning with S.

The end of the formula should have a comma, then a value (maybe a cell reference) and loads of close brackets... that is the value it returns if all the tests are negative...

Hope that helps...
 
Upvote 0
1st test - If D2<699, return content of S2

If the above condition isn't met...

2nd test - If D2<799, return content of S3

If the above condition isn't met...

3rd test - If D2<899, content of S4

etc
etc
etc
 
Upvote 0
thats great thanks

but it doesnt seem to do what i wanted i dont think

how do i count the number of certain values? ie go through a list of numbers and count the numbers say between 600 and 700, 700 and 800, 800 and 900

so i can end up with something like this

600 - 700 ---- 3
700 - 800 ---- 4
800 - 900 ---- 10
 
Upvote 0
Example attached below
Book3
ABCDEFG
1
2709600-6994
3787700-7997
4671800-8996
5789
6882
7844
8643
9856
10620
11753
12754
13774
14892
15673
16794
17820
18832
19
20
Sheet1


Hope that helps
 
Upvote 0

Forum statistics

Threads
1,213,491
Messages
6,113,963
Members
448,536
Latest member
CantExcel123

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