If then function analyzing changes in quantities and prices between data sets

[Waboku]

Hello, thank you for considering helping me with my problem.

I would like and appreciate a function that does the following for my work.

A function that enters the following values into a cell if the cells it is referencing meets a criteria.

1 if C2 is blank or zero

2 if C2 is 9 or more less than E2 and B2 equals D2

3 if E2 is 9 or higher and C2 is less than 9 and B2 is less than D2

4 if E2 is 9 or higher and C2 is less than 9 and B2 is greater than D2

5 if C2 is 9 or higher and is greater than E2 and B2 is less than D2

6 if C2 is 9 or higher and is greater than E2 and B2 is greater than D2

7 if C2 is 9 or more greater than E2 and B2 equals D2

8 if E2 was blank or zero and C2 is at least 9

and also a function that does the following:

equals G2 if there is a G2 otherwise equals F2

 

[BrianJN1]

For the last part try:
=IF(OR(G2=0,G2=""),F2,G2)

I am assuming that you mean that G2 is may be either 0 or blank otherwise that will be:
=if(G2="",F2,G2)

Now that more complex function seems to be a worry. I believe that one can only nest 7 IF conditions in a formula and you are asking for 8 unless one of those IFs is what you want as the default.

 

[Waboku]

Thanks Brian, that did work for the last part. If someone else considers helping with this but the number of if conditions is a problem i could combine some of the different if's into an or statement instead of an if. Specifically 1 and 2 could be one if/or and so could 7 and 8.

 

[BrianJN1]

I presume that you understand how an =IF(AND(.... and an =IF(OR(..... statement is constructed
as well as a nest of IFs (=IF(Condition,Result,IF(NewCondition,NewResult....

A redefinition of your conditions may help providing it does not conflict with the logic which you have already proposed.
There is just one last requirement however, if no condition is met what will be the default of the cell, 0, "", or the value of some other column?

 

[Peter_SSs]

For the second part, to my mind, if G2 holds a zero then "there is a G2" is met so zero should be returned.
So, =IF(G2="",F2,G2)

However, if Brian's formula does what you want, then perhaps it was just that your description is a little ambiguous.


For the first part..
- Unless you are using Excel 2003 or earlier, there is no 7 nesting levels limit.
- Generally, when listing conditions in order like you have, you want them applied in that order. So, if the various cells meet more than one of the conditions, the first one encountered is the one that is applied. Is that what you want?
For example
B2=5
C2=20
D2=5
E2 = blank

This set of conditions meets both 7 and 8. My formula below would return 7 for this circumstance because it is the first met of 7 and 8.

- I'm wondering if you have deliberately missed some conditions?
Consider numbers 3 and 4. The first parts of these are the same, the only difference is the comparison of B2 and D2. But what you have described does not say what to do if the first part of those two are true and B2 = D2

Anyway, I think this applies what you have written, in the order you have written it. If none of the conditions are met then the formula returns ""

=IF(OR(C2={"",0}),1,IF(AND(C2<=E2-9,B2=D2),2,IF(AND(E2>=9,C2<9,B2<D2),3,IF(AND(E2>=9,C2<9,B2>D2),4,IF(AND(C2>=9,C2>E2,B2<D2),5,IF(AND(C2>=9,C2>E2,B2>D2),6,IF(AND(C2>=E2+9,B2=D2),7,IF(AND(OR(E2={"",0}),C2>=9),8,""))))))))

 

[BrianJN1]

@Peter_SSs
That was an enlightenment, the number of nested IFs. Thanks.

As to my formulae:
=IF(OR(G2=0,G2=""),F2,G2)
and
=if(G2="",F2,G2)

In relation to "equals G2 if there is a G2 otherwise equals F2" I considered that if G2 was void then so should be the destination even though I felt that "0" should be the result anyway. I actually trialed both of the formulae and Excel offered a blank for the first. Going back now both give me "0" (and no! I didn't set conditional formats!).

 

[Waboku]

Hello,

Peter,

Thank you for making that function for me. With your function I would be using your =IF(G2="",F2,G2) on the results, I think I could have been more specific in my request. A function that applies the conditions in the order that they appeared did work for me. If it isn't too much trouble would you please consider making an updated version of this function for me? With the function you designed I was able to see what some of the problems were in my request. I think to explain why it looks like some of the conditions were missed is that B&C and D&E are related, there is no B without a C and the same for D & E, otherwise I think a function that applies the results to the first condition that is met works. Thank you again for your help and if you don't mind, please, the following is what I would like help with:

I'm sorry to ask for your help again after you helped me with my problem.

I would like a function that does this in order:

[TABLE="width: 717"]
<colgroup><col></colgroup><tbody>[TR]
[TD]1 if C2 is blank or zero
[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD]2 If E2 is 9 or higher and C2 is Less than E2 and C2 is 9 or lower and B2 is less than D2
[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD]3 If E2 is 9 or higher and C2 is Less than E2 and C2 is 9 or lower and B2 is greater than D2
[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD]4 If C2 is 9 or higher and E2 is less than C2 and E2 is 9 or lower and B2 is less than D2
[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD]5 If C2 is 9 or higher and E2 is less than C2 and E2 is 9 or lower and B2 is greater than D2
[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD]6 if C2 is 9 or more greater than E2
[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD]7 if C2 is 9 or more less than E2
[/TD]
[/TR]
</tbody>[/TABLE]

Thank you for considering.

Brian,

I don't have any knowledge of If functions, I just thought it was possible to combine some of the statements. I am fairly new to Excel. Thanks for your help.

 

[Peter_SSs]

I believe that this formula covers those conditions in order

=IF(OR(C2={"",0}),1,IF(AND(E2>=9,C2<E2,C2<=9,B2<D2),2,IF(AND(E2>=9,C2<E2,C2<=9,B2>D2),3,IF(AND(C2>=9,E2<C2,E2<=9,B2<D2),4,IF(AND(C2>=9,E2<C2,E2<=9,B2>D2),5,IF(C2>=E2+9,6,IF(C2<=E2-9,7,"Conditions not covered")))))))

However, you didn't address my previous question:

I'm wondering if you have deliberately missed some conditions?
Consider numbers 3 and 4. The first parts of these are the same, the only difference is the comparison of B2 and D2. But what you have described does not say what to do if the first part of those two are true and B2 = D2

For example, with your conditions listed in post #7 , what should happen if the following occurs?
B2=5
C2=10
D2=5
E2=11
This set of conditions is not covered by any of your 7 listed scenarios.
Or would that combination of values be impossible with your data?

 

[Waboku]

Hello Peter,

Thank you for the new formula. It does exactly what I wanted.

I'm sorry I missed your previous question. I did deliberately miss some conditions. Column C represents the new quantity of an item, Column E represents the previous days quantity of the item. Column B is the current price and column D is the previous days price. Various situations similar to your example above do occur and like you said are not covered by the function, when that occurs I would like the result to be blank, I was able to see how that could be adjusted in your function. I'm focusing solely on the quantity changes to or from at least 9 or higher quantities, quantity changes of at least 9, and when items have a new quantity of zero. Based on my analysis of the price and quantity changes that are different from the ones covered by the function, I can't make a good assessment of what to do with an item when that occurs. What I'm doing instead is keeping a list of which one of the outcomes from the function you made happened most recently for an item to decide what to do with the item because I consider those changes to be the relevant factors for determining what to do. Thank you again.

 

[Peter_SSs]

Thanks for the clarification. All sorted!