Microsoft excel nested IF Menu - where is the logical error?
up vote
0
down vote
favorite
I have a problem with the nested menu.
On one hand, I have the count of sold items. From 0 to more than 50 000 item.
From the other hand, with the increase of the count, the price drops down.
A is the line of quantity, B is the line of price.
A20 1000 0.90 B20
A21 2000 0.80 B21
A22 5000 0.70 B22
A23 10000 0.60 B23
A24 20000 0.50 B24
A25 30000 0.40 B25
A26 50000 0.30 B26
And the cell where the end result should be illustrated is B13
I put the following formula:
IF(B13<A2,B13*B20,IF(B13>A21<A22,B13*B21,IF(B13>A22<A23,B13*B22,IF(B13>A23<A24,B13*B23,IF(B13>A24<A25,B13*B24,IF(B13>A25<A26,B13*B25,IF(B13>A26,B13*B26,"0")))))))
But already after the threshold 2000 (e.g. 2001) I get the result 0 (which is my false value).
Where is my mistake?
microsoft-excel
edited Nov 19 at 14:24
bummi
1,50731421
asked Nov 19 at 13:40
Provangelist
11
add a comment |
up vote
0
down vote
favorite
I have a problem with the nested menu.
On one hand, I have the count of sold items. From 0 to more than 50 000 item.
From the other hand, with the increase of the count, the price drops down.
A is the line of quantity, B is the line of price.
A20 1000 0.90 B20
A21 2000 0.80 B21
A22 5000 0.70 B22
A23 10000 0.60 B23
A24 20000 0.50 B24
A25 30000 0.40 B25
A26 50000 0.30 B26
And the cell where the end result should be illustrated is B13
I put the following formula:
IF(B13<A2,B13*B20,IF(B13>A21<A22,B13*B21,IF(B13>A22<A23,B13*B22,IF(B13>A23<A24,B13*B23,IF(B13>A24<A25,B13*B24,IF(B13>A25<A26,B13*B25,IF(B13>A26,B13*B26,"0")))))))
But already after the threshold 2000 (e.g. 2001) I get the result 0 (which is my false value).
Where is my mistake?
microsoft-excel
edited Nov 19 at 14:24
bummi
1,50731421
asked Nov 19 at 13:40
Provangelist
11
1
Shouldn't the first condition not beB13<A20(A20instead ofA2)? You may also want to look intoVLOOKUPand/orINDEX/MATCH
– cybernetic.nomad
Nov 19 at 15:05
1
If(B13>A21<A22..., that's not the proper construction. You needAND():IF(AND(B13>A21,A21<A22),...You can test this by putting1inA1, and inB1, put=IF(1>A1>2,"Yes","No"), will always returnYesno matter what value you put inA1.
– BruceWayne
Nov 19 at 17:57
Thanks I decided to use the Vlookup function at the end. Later I fixed the problem, but there was again a logical error.
– Provangelist
Nov 20 at 15:33
Note that the pricing system you describe doesn't make much sense in the real world (e.g. 999 items would cost 999 * 0.9 = 889.1, while 1000 items would only cost 1000 * 0.8 = 800). The more usual system is to say that the first 1000 items cost 0.9 each and the next 1000 items cost 0.8 etc.)
– Blackwood
Nov 20 at 17:17
add a comment |
up vote
0
down vote
favorite
up vote
0
down vote
favorite
I have a problem with the nested menu.
On one hand, I have the count of sold items. From 0 to more than 50 000 item.
From the other hand, with the increase of the count, the price drops down.
A is the line of quantity, B is the line of price.
A20 1000 0.90 B20
A21 2000 0.80 B21
A22 5000 0.70 B22
A23 10000 0.60 B23
A24 20000 0.50 B24
A25 30000 0.40 B25
A26 50000 0.30 B26
And the cell where the end result should be illustrated is B13
I put the following formula:
IF(B13<A2,B13*B20,IF(B13>A21<A22,B13*B21,IF(B13>A22<A23,B13*B22,IF(B13>A23<A24,B13*B23,IF(B13>A24<A25,B13*B24,IF(B13>A25<A26,B13*B25,IF(B13>A26,B13*B26,"0")))))))
But already after the threshold 2000 (e.g. 2001) I get the result 0 (which is my false value).
Where is my mistake?
microsoft-excel
edited Nov 19 at 14:24
bummi
1,50731421
asked Nov 19 at 13:40
Provangelist
11
I have a problem with the nested menu.
On one hand, I have the count of sold items. From 0 to more than 50 000 item.
From the other hand, with the increase of the count, the price drops down.
A is the line of quantity, B is the line of price.
A20 1000 0.90 B20
A21 2000 0.80 B21
A22 5000 0.70 B22
A23 10000 0.60 B23
A24 20000 0.50 B24
A25 30000 0.40 B25
A26 50000 0.30 B26
And the cell where the end result should be illustrated is B13
I put the following formula:
IF(B13<A2,B13*B20,IF(B13>A21<A22,B13*B21,IF(B13>A22<A23,B13*B22,IF(B13>A23<A24,B13*B23,IF(B13>A24<A25,B13*B24,IF(B13>A25<A26,B13*B25,IF(B13>A26,B13*B26,"0")))))))
But already after the threshold 2000 (e.g. 2001) I get the result 0 (which is my false value).
Where is my mistake?
microsoft-excel
microsoft-excel
edited Nov 19 at 14:24
bummi
1,50731421
asked Nov 19 at 13:40
Provangelist
11
edited Nov 19 at 14:24
bummi
1,50731421
asked Nov 19 at 13:40
Provangelist
11
edited Nov 19 at 14:24
bummi
1,50731421
edited Nov 19 at 14:24
bummi
1,50731421
edited Nov 19 at 14:24
bummi
1,50731421
1,50731421
asked Nov 19 at 13:40
Provangelist
11
asked Nov 19 at 13:40
Provangelist
11
asked Nov 19 at 13:40
Provangelist
11
11
1
Shouldn't the first condition not beB13<A20(A20instead ofA2)? You may also want to look intoVLOOKUPand/orINDEX/MATCH
– cybernetic.nomad
Nov 19 at 15:05
1
If(B13>A21<A22..., that's not the proper construction. You needAND():IF(AND(B13>A21,A21<A22),...You can test this by putting1inA1, and inB1, put=IF(1>A1>2,"Yes","No"), will always returnYesno matter what value you put inA1.
– BruceWayne
Nov 19 at 17:57
Thanks I decided to use the Vlookup function at the end. Later I fixed the problem, but there was again a logical error.
– Provangelist
Nov 20 at 15:33
Note that the pricing system you describe doesn't make much sense in the real world (e.g. 999 items would cost 999 * 0.9 = 889.1, while 1000 items would only cost 1000 * 0.8 = 800). The more usual system is to say that the first 1000 items cost 0.9 each and the next 1000 items cost 0.8 etc.)
– Blackwood
Nov 20 at 17:17
add a comment |
1
Shouldn't the first condition not beB13<A20(A20instead ofA2)? You may also want to look intoVLOOKUPand/orINDEX/MATCH
– cybernetic.nomad
Nov 19 at 15:05
1
If(B13>A21<A22..., that's not the proper construction. You needAND():IF(AND(B13>A21,A21<A22),...You can test this by putting1inA1, and inB1, put=IF(1>A1>2,"Yes","No"), will always returnYesno matter what value you put inA1.
– BruceWayne
Nov 19 at 17:57
Thanks I decided to use the Vlookup function at the end. Later I fixed the problem, but there was again a logical error.
– Provangelist
Nov 20 at 15:33
Note that the pricing system you describe doesn't make much sense in the real world (e.g. 999 items would cost 999 * 0.9 = 889.1, while 1000 items would only cost 1000 * 0.8 = 800). The more usual system is to say that the first 1000 items cost 0.9 each and the next 1000 items cost 0.8 etc.)
– Blackwood
Nov 20 at 17:17
1
1
Shouldn't the first condition not be
B13<A20 (A20 instead of A2)? You may also want to look into VLOOKUP and/or INDEX/MATCH– cybernetic.nomad
Nov 19 at 15:05
Shouldn't the first condition not be
B13<A20 (A20 instead of A2)? You may also want to look into VLOOKUP and/or INDEX/MATCH– cybernetic.nomad
Nov 19 at 15:05
1
1
If(B13>A21<A22..., that's not the proper construction. You need AND(): IF(AND(B13>A21,A21<A22),... You can test this by putting 1 in A1, and in B1, put =IF(1>A1>2,"Yes","No"), will always return Yes no matter what value you put in A1.– BruceWayne
Nov 19 at 17:57
If(B13>A21<A22..., that's not the proper construction. You need AND(): IF(AND(B13>A21,A21<A22),... You can test this by putting 1 in A1, and in B1, put =IF(1>A1>2,"Yes","No"), will always return Yes no matter what value you put in A1.– BruceWayne
Nov 19 at 17:57
Thanks I decided to use the Vlookup function at the end. Later I fixed the problem, but there was again a logical error.
– Provangelist
Nov 20 at 15:33
Thanks I decided to use the Vlookup function at the end. Later I fixed the problem, but there was again a logical error.
– Provangelist
Nov 20 at 15:33
Note that the pricing system you describe doesn't make much sense in the real world (e.g. 999 items would cost 999 * 0.9 = 889.1, while 1000 items would only cost 1000 * 0.8 = 800). The more usual system is to say that the first 1000 items cost 0.9 each and the next 1000 items cost 0.8 etc.)
– Blackwood
Nov 20 at 17:17
Note that the pricing system you describe doesn't make much sense in the real world (e.g. 999 items would cost 999 * 0.9 = 889.1, while 1000 items would only cost 1000 * 0.8 = 800). The more usual system is to say that the first 1000 items cost 0.9 each and the next 1000 items cost 0.8 etc.)
– Blackwood
Nov 20 at 17:17
add a comment |
active
oldest
votes
active
oldest
votes
active
oldest
votes
active
oldest
votes
active
oldest
votes
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fsuperuser.com%2fquestions%2f1376702%2fmicrosoft-excel-nested-if-menu-where-is-the-logical-error%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
1
Shouldn't the first condition not be
B13<A20(A20instead ofA2)? You may also want to look intoVLOOKUPand/orINDEX/MATCH– cybernetic.nomad
Nov 19 at 15:05
1
If(B13>A21<A22..., that's not the proper construction. You needAND():IF(AND(B13>A21,A21<A22),...You can test this by putting1inA1, and inB1, put=IF(1>A1>2,"Yes","No"), will always returnYesno matter what value you put inA1.– BruceWayne
Nov 19 at 17:57
Thanks I decided to use the Vlookup function at the end. Later I fixed the problem, but there was again a logical error.
– Provangelist
Nov 20 at 15:33
Note that the pricing system you describe doesn't make much sense in the real world (e.g. 999 items would cost 999 * 0.9 = 889.1, while 1000 items would only cost 1000 * 0.8 = 800). The more usual system is to say that the first 1000 items cost 0.9 each and the next 1000 items cost 0.8 etc.)
– Blackwood
Nov 20 at 17:17