Add condition to existing formula

[Ozhatch]

I have this formula in column K: =IF(AND(H6>=1000,J6<=10%),"Yes","No") Now I want to add a further condition.

If the word "None" appears in column E, Column K should return "No". In any other condition in column E, column K should return "Yes".

Please would some kind expert edit the formula for me?

Thanks very much.

 

[steve the fish]

That doesn't make sense. If 'None' in column E returns 'No' and anything else returns 'Yes' then the current formula is redundant. What is it you mean?

 

[Ozhatch]

Thanks, Steve.

Column K already has several conditions for returning 'Yes' or 'No'. Now, if the word "None" appears in column E, and the other conditions in column K are taken into account, then Column K should return "No". In any other condition in column E, and the other conditions in column K are taken into account, column K should return "Yes".

I hope that clears it up.

I can post a sample sheet to box.com if that would help.

 

[Fluff]

Is this what you mean
=IF(AND(H6>=1000,J6<=10%,E6<>"None"),"Yes","No")

 

[Ozhatch]

That's great, Fluff! Thanks so much.

There is a wrinkle, though, which can best be explained if the sheet could be made available, as there are other cells which affect the outcome. For instance, if a particular cell is empty, or shows '0', the result in column K is incorrect.

The file is at https://app.box.com/s/f7e9xvs72tykob972ibtnjp536r0329s If that's unacceptable, please let me know.

 

[Fluff]

There is nothing on that sheet with "None"
Also can you please show a manual mock of what you want for each scenario

 

[Ozhatch]

There is nothing on that sheet with "None"
Also can you please show a manual mock of what you want for each scenario

I've uploaded a screen shot (PNG) to my Box account. https://app.box.com/s/1oyrrcuj5eznstivejyee2jbf4nxc2er

I don't know what you mean by a manual mock, so I've compiled a list of functions in each cell:

Columns B, C, D, F and H: Values are entered by the user.
Column E Drop-down: List is • Cash to driver; • None; • On invoice; • Store pick up
Column I =IFERROR(F6+G6,"")
Column J =IFERROR(G5/I5,"")
Column K =IF(AND(H5>=1000,J5<=10%),"Yes","No")^ª
Column L =IF(K5="YES",H5*4%,"")

^ª This is the cell in question. If column G =0, or is blank, this cell should show “Yes”, unless Column E shows “None”, in which case it should show “No”.

 

[Ozhatch]

Correction! In each of the formulae above, the row # is 6, not 5.

Column J =IFERROR(G6/I6,"")
Column K =IF(AND(H6>=1000,J6<=10%),"Yes","No")
Column L =IF(K6="YES",H6*4%,"")

 

[Fluff]

Originally you wanted to add one more criteria to the formula, now you have added another (namely col G being blank).
Also when you say

This is the cell in question. If column G =0, or is blank, this cell should show “Yes”, unless Column E shows “None”, in which case it should show “No”.

Do you mean that the result of cols H & J are now irrelevant?

 

[Ozhatch]

Column H is user-entry, and column J is automatically calculated.
Bonuses are payable in any of the cases in Column E, except in the case of "None".
Column G is user-entry. In the case where no discount has been given, (cell is empty, or user has entered 0) column K should show "Yes" unless column E shows "None", in which case column K should show "No".