Hello,

I have an inventory of over 6000 items. In order to do a systematic cycle count, I would like to breakdown the inventory in multiple groups based on the unit cost of the item, such as:

Selection Criteria Unit Cost
1 = $5,000
2 = $3000 <$5000
3 = $2000 <$3000
4 = $1300 <$2000
5 = $1000 <$1300
6 = $800 <$1000
7 = $600 <$800
8 = $400 <$600
9 = $200 <$400
10 = $100 <$200


In order to be able to select the items based on any desired criteria (either thru pivot table or filter button), I was thinking of adding a column named "Selection Criteria" and having in that column a formula that would derive the correct narration such as "=$5,000", "=$3000 <$5000", so on and so forth.

I would appreciate if someone can someone help in writing the expression. As a test, I wrote the expression as follows, which did not work. A2 has a value of 3557 and i was expecting to see a Y.

=IF(A2=3000<4000,"Y","N")

If someone can suggets a better method, i would appreciate.

Regards
SJ

On Apr 3, 11:37 am, "SSJ" wrote:
If someone can suggets a better method, i would appreciate

I do not fully understand the problem you want to solve, but the
following might help with some of the details.

As a test, I wrote the expression as follows, which did not work. A2 has a
value of 3557 and i was expecting to see a Y.

=IF(A2=3000<4000,"Y","N")

I believe the expression you want above is:

=if(and(A2=3000, A2<4000), "Y", "N")

I have an inventory of over 6000 items. In order to do a
systematic cycle count, I would like to breakdown the
inventory in multiple groups based on the unit cost of the
item, such as:

Selection Criteria Unit Cost
1 = $5,000
2 = $3000 <$5000
3 = $2000 <$3000
4 = $1300 <$2000
5 = $1000 <$1300
6 = $800 <$1000
7 = $600 <$800
8 = $400 <$600
9 = $200 <$400
10 = $100 <$200

I wonder if the following solves your problem. In each row, put a
formula similar to the following:

=countif(A1:A6000, "=100") - countif(A1:A6000, "=200")

That satisfies the conditions in row 10 above.

It looks like you have too many arguments here for a nested if function, you
might have to do a lookup function.
a b
1 100 1
2 200 2
3 400 3
4 600 4
5 800 5
6 1000 6
7 1300 7
8 2000 8
9 3000 9
10 5000 10
11
12 3557 =IF(A12<100,"",VLOOKUP(A12,$A$1:$B$10,2))

"SSJ" wrote:

Hello,

I have an inventory of over 6000 items. In order to do a systematic cycle count, I would like to breakdown the inventory in multiple groups based on the unit cost of the item, such as:

Selection Criteria Unit Cost
1 = $5,000
2 = $3000 <$5000
3 = $2000 <$3000
4 = $1300 <$2000
5 = $1000 <$1300
6 = $800 <$1000
7 = $600 <$800
8 = $400 <$600
9 = $200 <$400
10 = $100 <$200


In order to be able to select the items based on any desired criteria (either thru pivot table or filter button), I was thinking of adding a column named "Selection Criteria" and having in that column a formula that would derive the correct narration such as "=$5,000", "=$3000 <$5000", so on and so forth.

I would appreciate if someone can someone help in writing the expression. As a test, I wrote the expression as follows, which did not work. A2 has a value of 3557 and i was expecting to see a Y.

=IF(A2=3000<4000,"Y","N")

If someone can suggets a better method, i would appreciate.

Regards
SJ

As you have 10 conditions, you will find it difficult to combine IF
statements to cover them all. I would suggest you use a lookup table -
enter the following, for example in cells X1 to Y10. Note that I have
put an apostrophe in front of the = sign in column Y - this is to
prevent Excel from trying to evaluate what it thinks is a formula (you
could pre-format the cells as Text first)

X Y
100 '= $100 <$200
200 '= $200 <$400
400 '= $400 <$600
600 '= $600 <$800
800 '= $800 <$1000
1000 '= $1000 <$1300
1300 '= $1300 <$2000
2000 '= $2000 <$3000
3000 '= $3000 <$5000
5000 '= $5,000

Then, assuming your unit cost data is in column G, this formula on row
2 will return the appropriate narration:

=VLOOKUP(G2,X$1:Y$10,2)

Copy this formula down your 6000 rows.

Hope this helps.

Pete


On Apr 3, 8:37 pm, "SSJ" wrote:
Hello,

I have an inventory of over 6000 items. In order to do a systematic cycle count, I would like to breakdown the inventory in multiple groups based on the unit cost of the item, such as:

Selection Criteria Unit Cost
1 = $5,000
2 = $3000 <$5000
3 = $2000 <$3000
4 = $1300 <$2000
5 = $1000 <$1300
6 = $800 <$1000
7 = $600 <$800
8 = $400 <$600
9 = $200 <$400
10 = $100 <$200

In order to be able to select the items based on any desired criteria (either thru pivot table or filter button), I was thinking of adding a column named "Selection Criteria" and having in that column a formula that would derive the correct narration such as "=$5,000", "=$3000 <$5000", so on and so forth.

I would appreciate if someone can someone help in writing the expression. As a test, I wrote the expression as follows, which did not work. A2 has a value of 3557 and i was expecting to see a Y.

=IF(A2=3000<4000,"Y","N")

If someone can suggets a better method, i would appreciate.

Regards
SJ

Pete,

Thank you for your response. The solution did not work. Perhaps my
explanation was not clear enough.

Following is a sample of my inventory (many fields are deleted to show only
the necessary data for the problem at hand) and, hopefully, a clearer
explanation:

1) Every week I do a cycle count of the inventory. As the number of items
are over 6000, I divided the entire inventory in 10 groups based on the unit
cost, hence, the selection criteria. The selection criteria give a range of
the unit cost. Every week I count the few groups depending on the number of
item in a group.

2) Currently what I do is dump the download into a worksheet. Using Auto
Filter I would select and the items based on a criteria and then copy them
into another sheet and then with the help of the pivot table organize that
data. If I intend to count four groups, then I would do the same excerise
the 4 times.

3) The idea is to be able to easily select the items based on a specific
criteria right from the main download and avoid all this auto filter
selection and copying, etc. Through pivot table I should then be able to
select a specific criteria which would list all the items in that range. The
data in the column "selection criteria" below is how the result should be.


PART DESCRIPTION QTY UOM UC @ A COST SELECTION CRITERIA
013-1636-010 BLACK BLADE ANTENNA 1 EA 6,308.34 6,308.34 =
$5,000
102A491 DC ELECTRIC MOTOR, 27V. 1 EA 3,322.35 3,322.35
= $3000 <$5000
10800B2F11 OXYGEN MASK ASSEMBLY 26 EA 2,798.40 72,758.33 =
$2000 <$3000
20732 DP CURRENT TRANSFORMER 1 EA 1,860.13 1,860.13
= $1300 <$2000
10706 GPS ANTENNA 7 EA 1,059.37 7,415.59 = $1000
<$1300
013-1969-040 TUNABLE GASKET,12",NTN 3 EA 562.82 1,688.46
= $400 <$600



Thanks
SJ



"Pete_UK" wrote in message
oups.com...
As you have 10 conditions, you will find it difficult to combine IF
statements to cover them all. I would suggest you use a lookup table -
enter the following, for example in cells X1 to Y10. Note that I have
put an apostrophe in front of the = sign in column Y - this is to
prevent Excel from trying to evaluate what it thinks is a formula (you
could pre-format the cells as Text first)

X Y
100 '= $100 <$200
200 '= $200 <$400
400 '= $400 <$600
600 '= $600 <$800
800 '= $800 <$1000
1000 '= $1000 <$1300
1300 '= $1300 <$2000
2000 '= $2000 <$3000
3000 '= $3000 <$5000
5000 '= $5,000

Then, assuming your unit cost data is in column G, this formula on row
2 will return the appropriate narration:

=VLOOKUP(G2,X$1:Y$10,2)

Copy this formula down your 6000 rows.

Hope this helps.

Pete


On Apr 3, 8:37 pm, "SSJ" wrote:
Hello,

I have an inventory of over 6000 items. In order to do a systematic cycle
count, I would like to breakdown the inventory in multiple groups based
on the unit cost of the item, such as:

Selection Criteria Unit Cost
1 = $5,000
2 = $3000 <$5000
3 = $2000 <$3000
4 = $1300 <$2000
5 = $1000 <$1300
6 = $800 <$1000
7 = $600 <$800
8 = $400 <$600
9 = $200 <$400
10 = $100 <$200

In order to be able to select the items based on any desired criteria
(either thru pivot table or filter button), I was thinking of adding a
column named "Selection Criteria" and having in that column a formula
that would derive the correct narration such as "=$5,000", "=$3000
<$5000", so on and so forth.

I would appreciate if someone can someone help in writing the expression.
As a test, I wrote the expression as follows, which did not work. A2 has
a value of 3557 and i was expecting to see a Y.

=IF(A2=3000<4000,"Y","N")

If someone can suggets a better method, i would appreciate.

Regards
SJ

The table is a bit difficult to follow because of line wrap, but I
think you have a column "COST" which seems to be the "QTY" column
times the "UC @ A" column. Next to this you want the "SELECTION
CRITERIA" column, made up of the narrative descriptions given in
column Y of my suggested table.

If you put the table where I suggested (X1 to Y10), then using the cut-
down layout in this latest example, you would be wanting to search on
the fifth column. The formula would then become:

=VLOOKUP(E2,X$1:Y$10,2)

entered into G2, and this should return

= $5,000

in that cell. When copied down it will return the other appropriate
values in the cells in that column. If this does not work for you then
please explain in a bit more detail what is happening. If you cannot
put the table in X1:Y10, then put it somewhere else (eg BB1:BC10) and
change the references in the formula, i.e.:

=VLOOKUP(E2,BB$1:BC$10,2)

Please come back if this does not work for you.

Pete

On Apr 4, 3:44 pm, "SSJ" wrote:
Pete,

Thank you for your response. The solution did not work. Perhaps my
explanation was not clear enough.

Following is a sample of my inventory (many fields are deleted to show only
the necessary data for the problem at hand) and, hopefully, a clearer
explanation:

1) Every week I do a cycle count of the inventory. As the number of items
are over 6000, I divided the entire inventory in 10 groups based on the unit
cost, hence, the selection criteria. The selection criteria give a range of
the unit cost. Every week I count the few groups depending on the number of
item in a group.

2) Currently what I do is dump the download into a worksheet. Using Auto
Filter I would select and the items based on a criteria and then copy them
into another sheet and then with the help of the pivot table organize that
data. If I intend to count four groups, then I would do the same excerise
the 4 times.

3) The idea is to be able to easily select the items based on a specific
criteria right from the main download and avoid all this auto filter
selection and copying, etc. Through pivot table I should then be able to
select a specific criteria which would list all the items in that range. The
data in the column "selection criteria" below is how the result should be.

PART DESCRIPTION QTY UOM UC @ A COST SELECTION CRITERIA
013-1636-010 BLACK BLADE ANTENNA 1 EA 6,308.34 6,308.34 =
$5,000
102A491 DC ELECTRIC MOTOR, 27V. 1 EA 3,322.35 3,322.35
= $3000 <$5000
10800B2F11 OXYGEN MASK ASSEMBLY 26 EA 2,798.40 72,758.33 =
$2000 <$3000
20732 DP CURRENT TRANSFORMER 1 EA 1,860.13 1,860.13
= $1300 <$2000
10706 GPS ANTENNA 7 EA 1,059.37 7,415.59 = $1000
<$1300
013-1969-040 TUNABLE GASKET,12",NTN 3 EA 562.82 1,688.46
= $400 <$600

Thanks
SJ

"Pete_UK" wrote in message

oups.com...



As you have 10 conditions, you will find it difficult to combine IF
statements to cover them all. I would suggest you use a lookup table -
enter the following, for example in cells X1 to Y10. Note that I have
put an apostrophe in front of the = sign in column Y - this is to
prevent Excel from trying to evaluate what it thinks is a formula (you
could pre-format the cells as Text first)

X Y
100 '= $100 <$200
200 '= $200 <$400
400 '= $400 <$600
600 '= $600 <$800
800 '= $800 <$1000
1000 '= $1000 <$1300
1300 '= $1300 <$2000
2000 '= $2000 <$3000
3000 '= $3000 <$5000
5000 '= $5,000

Then, assuming your unit cost data is in column G, this formula on row
2 will return the appropriate narration:

=VLOOKUP(G2,X$1:Y$10,2)

Copy this formula down your 6000 rows.

Hope this helps.

Pete

On Apr 3, 8:37 pm, "SSJ" wrote:
Hello,

I have an inventory of over 6000 items. In order to do a systematic cycle
count, I would like to breakdown the inventory in multiple groups based
on the unit cost of the item, such as:

Selection Criteria Unit Cost
1 = $5,000
2 = $3000 <$5000
3 = $2000 <$3000
4 = $1300 <$2000
5 = $1000 <$1300
6 = $800 <$1000
7 = $600 <$800
8 = $400 <$600
9 = $200 <$400
10 = $100 <$200

In order to be able to select the items based on any desired criteria
(either thru pivot table or filter button), I was thinking of adding a
column named "Selection Criteria" and having in that column a formula
that would derive the correct narration such as "=$5,000", "=$3000
<$5000", so on and so forth.

I would appreciate if someone can someone help in writing the expression.
As a test, I wrote the expression as follows, which did not work. A2 has
a value of 3557 and i was expecting to see a Y.

=IF(A2=3000<4000,"Y","N")

If someone can suggets a better method, i would appreciate.

Regards
SJ- Hide quoted text -

- Show quoted text -

Pete,

It worked beautifully. That is EXACTLY what i was looking for. Amazing!
Please do me a favor and explain to me the connection betwen the formula &
the table.

I understand the vlookup formula. It is also clear that the column Y
contains text. SO what is the role of column X? If the unit cost is $350.99,
how does it returns the text "=$200<$400"? How is it measuring?

Regards
SJ


"Pete_UK" wrote in message
oups.com...
The table is a bit difficult to follow because of line wrap, but I
think you have a column "COST" which seems to be the "QTY" column
times the "UC @ A" column. Next to this you want the "SELECTION
CRITERIA" column, made up of the narrative descriptions given in
column Y of my suggested table.

If you put the table where I suggested (X1 to Y10), then using the cut-
down layout in this latest example, you would be wanting to search on
the fifth column. The formula would then become:

=VLOOKUP(E2,X$1:Y$10,2)

entered into G2, and this should return

= $5,000

in that cell. When copied down it will return the other appropriate
values in the cells in that column. If this does not work for you then
please explain in a bit more detail what is happening. If you cannot
put the table in X1:Y10, then put it somewhere else (eg BB1:BC10) and
change the references in the formula, i.e.:

=VLOOKUP(E2,BB$1:BC$10,2)

Please come back if this does not work for you.

Pete

On Apr 4, 3:44 pm, "SSJ" wrote:
Pete,

Thank you for your response. The solution did not work. Perhaps my
explanation was not clear enough.

Following is a sample of my inventory (many fields are deleted to show
only
the necessary data for the problem at hand) and, hopefully, a clearer
explanation:

1) Every week I do a cycle count of the inventory. As the number of items
are over 6000, I divided the entire inventory in 10 groups based on the
unit
cost, hence, the selection criteria. The selection criteria give a range
of
the unit cost. Every week I count the few groups depending on the number
of
item in a group.

2) Currently what I do is dump the download into a worksheet. Using Auto
Filter I would select and the items based on a criteria and then copy
them
into another sheet and then with the help of the pivot table organize
that
data. If I intend to count four groups, then I would do the same excerise
the 4 times.

3) The idea is to be able to easily select the items based on a specific
criteria right from the main download and avoid all this auto filter
selection and copying, etc. Through pivot table I should then be able to
select a specific criteria which would list all the items in that range.
The
data in the column "selection criteria" below is how the result should
be.

PART DESCRIPTION QTY UOM UC @ A COST SELECTION CRITERIA
013-1636-010 BLACK BLADE ANTENNA 1 EA 6,308.34 6,308.34
=
$5,000
102A491 DC ELECTRIC MOTOR, 27V. 1 EA 3,322.35
3,322.35
= $3000 <$5000
10800B2F11 OXYGEN MASK ASSEMBLY 26 EA 2,798.40 72,758.33
=
$2000 <$3000
20732 DP CURRENT TRANSFORMER 1 EA 1,860.13
1,860.13
= $1300 <$2000
10706 GPS ANTENNA 7 EA 1,059.37 7,415.59 =
$1000
<$1300
013-1969-040 TUNABLE GASKET,12",NTN 3 EA 562.82
1,688.46
= $400 <$600

Thanks
SJ

"Pete_UK" wrote in message

oups.com...



As you have 10 conditions, you will find it difficult to combine IF
statements to cover them all. I would suggest you use a lookup table -
enter the following, for example in cells X1 to Y10. Note that I have
put an apostrophe in front of the = sign in column Y - this is to
prevent Excel from trying to evaluate what it thinks is a formula (you
could pre-format the cells as Text first)

X Y
100 '= $100 <$200
200 '= $200 <$400
400 '= $400 <$600
600 '= $600 <$800
800 '= $800 <$1000
1000 '= $1000 <$1300
1300 '= $1300 <$2000
2000 '= $2000 <$3000
3000 '= $3000 <$5000
5000 '= $5,000

Then, assuming your unit cost data is in column G, this formula on row
2 will return the appropriate narration:

=VLOOKUP(G2,X$1:Y$10,2)

Copy this formula down your 6000 rows.

Hope this helps.

Pete

On Apr 3, 8:37 pm, "SSJ" wrote:
Hello,

I have an inventory of over 6000 items. In order to do a systematic
cycle
count, I would like to breakdown the inventory in multiple groups
based
on the unit cost of the item, such as:

Selection Criteria Unit Cost
1 = $5,000
2 = $3000 <$5000
3 = $2000 <$3000
4 = $1300 <$2000
5 = $1000 <$1300
6 = $800 <$1000
7 = $600 <$800
8 = $400 <$600
9 = $200 <$400
10 = $100 <$200

In order to be able to select the items based on any desired criteria
(either thru pivot table or filter button), I was thinking of adding a
column named "Selection Criteria" and having in that column a formula
that would derive the correct narration such as "=$5,000", "=$3000
<$5000", so on and so forth.

I would appreciate if someone can someone help in writing the
expression.
As a test, I wrote the expression as follows, which did not work. A2
has
a value of 3557 and i was expecting to see a Y.

=IF(A2=3000<4000,"Y","N")

If someone can suggets a better method, i would appreciate.

Regards
SJ- Hide quoted text -

- Show quoted text -

Glad it worked for you - thanks for feeding back.

The VLOOKUP formula I gave you does not have a 4th parameter, which
means that the table it looks through has to be in sequence and it
operates an "approximate" match rather than an exact match. In looking
down the first column of the lookup table, it will match on the
largest value which is less or equal to the lookup value.

So, if you are looking for 350.99, it will match with the 200 in the
second row of the table, as the next value (400) is too high. The
final parameter of the VLOOKUP formula, 2, determines which column of
the table the value is returned from, so the formula will then return
the value from the second column of the table on the same row as the
matching value, i.e.

"= $200 <$400".

Similarly, if you are looking for 749.99, this will match with the 4th
row of the table (600) and return "= $600 <$800".

Hope this explanation helps you to see how it works.

Pete


On Apr 5, 7:45 pm, "SSJ" wrote:
Pete,

It worked beautifully. That is EXACTLY what i was looking for. Amazing!
Please do me a favor and explain to me the connection betwen the formula &
the table.

I understand the vlookup formula. It is also clear that the column Y
contains text. SO what is the role of column X? If the unit cost is $350.99,
how does it returns the text "=$200<$400"? How is it measuring?

Regards
SJ

"Pete_UK" wrote in message

oups.com...



The table is a bit difficult to follow because of line wrap, but I
think you have a column "COST" which seems to be the "QTY" column
times the "UC @ A" column. Next to this you want the "SELECTION
CRITERIA" column, made up of the narrative descriptions given in
column Y of my suggested table.

If you put the table where I suggested (X1 to Y10), then using the cut-
down layout in this latest example, you would be wanting to search on
the fifth column. The formula would then become:

=VLOOKUP(E2,X$1:Y$10,2)

entered into G2, and this should return

= $5,000

in that cell. When copied down it will return the other appropriate
values in the cells in that column. If this does not work for you then
please explain in a bit more detail what is happening. If you cannot
put the table in X1:Y10, then put it somewhere else (eg BB1:BC10) and
change the references in the formula, i.e.:

=VLOOKUP(E2,BB$1:BC$10,2)

Please come back if this does not work for you.

Pete

On Apr 4, 3:44 pm, "SSJ" wrote:
Pete,

Thank you for your response. The solution did not work. Perhaps my
explanation was not clear enough.

Following is a sample of my inventory (many fields are deleted to show
only
the necessary data for the problem at hand) and, hopefully, a clearer
explanation:

1) Every week I do a cycle count of the inventory. As the number of items
are over 6000, I divided the entire inventory in 10 groups based on the
unit
cost, hence, the selection criteria. The selection criteria give a range
of
the unit cost. Every week I count the few groups depending on the number
of
item in a group.

2) Currently what I do is dump the download into a worksheet. Using Auto
Filter I would select and the items based on a criteria and then copy
them
into another sheet and then with the help of the pivot table organize
that
data. If I intend to count four groups, then I would do the same excerise
the 4 times.

3) The idea is to be able to easily select the items based on a specific
criteria right from the main download and avoid all this auto filter
selection and copying, etc. Through pivot table I should then be able to
select a specific criteria which would list all the items in that range.
The
data in the column "selection criteria" below is how the result should
be.

PART DESCRIPTION QTY UOM UC @ A COST SELECTION CRITERIA
013-1636-010 BLACK BLADE ANTENNA 1 EA 6,308.34 6,308.34
=
$5,000
102A491 DC ELECTRIC MOTOR, 27V. 1 EA 3,322.35
3,322.35
= $3000 <$5000
10800B2F11 OXYGEN MASK ASSEMBLY 26 EA 2,798.40 72,758.33
=
$2000 <$3000
20732 DP CURRENT TRANSFORMER 1 EA 1,860.13
1,860.13
= $1300 <$2000
10706 GPS ANTENNA 7 EA 1,059.37 7,415.59 =
$1000
<$1300
013-1969-040 TUNABLE GASKET,12",NTN 3 EA 562.82
1,688.46
= $400 <$600

Thanks
SJ

"Pete_UK" wrote in message

groups.com...

As you have 10 conditions, you will find it difficult to combine IF
statements to cover them all. I would suggest you use a lookup table -
enter the following, for example in cells X1 to Y10. Note that I have
put an apostrophe in front of the = sign in column Y - this is to
prevent Excel from trying to evaluate what it thinks is a formula (you
could pre-format the cells as Text first)

X Y
100 '= $100 <$200
200 '= $200 <$400
400 '= $400 <$600
600 '= $600 <$800
800 '= $800 <$1000
1000 '= $1000 <$1300
1300 '= $1300 <$2000
2000 '= $2000 <$3000
3000 '= $3000 <$5000
5000 '= $5,000

Then, assuming your unit cost data is in column G, this formula on row
2 will return the appropriate narration:

=VLOOKUP(G2,X$1:Y$10,2)

Copy this formula down your 6000 rows.

Hope this helps.

Pete

On Apr 3, 8:37 pm, "SSJ" wrote:
Hello,

I have an inventory of over 6000 items. In order to do a systematic
cycle
count, I would like to breakdown the inventory in multiple groups
based
on the unit cost of the item, such as:

Selection Criteria Unit Cost
1 = $5,000
2 = $3000 <$5000
3 = $2000 <$3000
4 = $1300 <$2000
5 = $1000 <$1300
6 = $800 <$1000
7 = $600 <$800
8 = $400 <$600
9 = $200 <$400
10 = $100 <$200

In order to be able to select the items based on any desired criteria
(either thru pivot table or filter button), I was thinking of adding a
column named "Selection Criteria" and having in that column a formula
that would derive the correct narration such as "=$5,000", "=$3000
<$5000", so on and so forth.

I would appreciate if someone can someone help in writing the
expression.
As a test, I wrote the expression as follows, which did not work. A2
has
a value of 3557 and i was expecting to see a Y.

=IF(A2=3000<4000,"Y","N")

If someone can suggets a better method, i would appreciate.

Regards
SJ- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -