Trouble combining two formulas and retaining both outputs.

[Johnny247]

Hi all,
I am struggling to combine two formulas which are essentially two halves of the same end result into one formula.
In one cell, I can get the formula to return a value if an amount is required, and in the other cell, I can get the formula to return a comment "Not Enough" if there is no amount or not enough available.
However, I cannot seem to combine both sets together to give me one formula.
I have attached a sample file and highlighted the cells in yellow.
M15 works as it should, O15 works as it should, but N15 is where I have tried to combine them and it's returning a value error.
Can anyone help on this please?

Thanks in advance.

[Glenn Kennedy]

What do you want the formula to do - it won't, as it is clearly incorrect... What is the expected answer manually how do you calculate it?

[Glenn Kennedy]

This looks odd:
=IF(M11="","",SUM(M11-M11*2))

Why not just:
IF(M11="","",-M11)

Does this work in N15???

=IF(M15<>"",IF(L15=0,"not enough",M15),"")

[Johnny247]

Thanks for your reply.
The expected outcome should be that it sums the cell in column "M" first of all (I have done it so that it converts from a negative number to positive number as it should read as a positive, although there is probably a more straightforward way!).
It should then look at the cell in column "H", and if less than column "I", return a value of 0, if more than column "I", add the cell in columns "I" and "L" together (on that row). If this is still less than the call in column "H", return the message "Not enough".

Hopefully that makes sense!

Thanks in advance.

[Mrrrr]

The sum you are using for the formula in N15 is not OK because if, like in your case, L15 is zero, then the SUM would be between -M15 and "not enough".
Plus, SUM(M15-M15*2) could actually be written without sum, just as -M15 because that's the result of M15-2*M15.

Maybe something like this is close to what you need:

Please Login or Register  to view this content.

[Johnny247]

Thanks for that. I have tried that, but it appears to come up with an error still?
Thanks

[Johnny247]

For ease of explanation, the purpose of column N is to convert the negative number to a positive number in column M as it reads wrong (it is supposed to read as a positive number in the first place).
If there is then a positive number in column N, the formula should then sum I & L (stock locations) and if the sum total is less than Column H (amount required) it should return the text Not enough.

[Glenn Kennedy]

Thanks for that. I have tried that, but it appears to come up with an error still?
Thanks

Who are you talking to?

see sheet

[Johnny247]

Hi Glenn, thanks for your reply. Unfortunately, that does not work. The sheet repeats what is already in column.
I have tried amending it to this: =IF(M15="","",-M15,IF(L15=0,"not enough",M15)) But still no joy.
Column N is a bit (in poor English!) like a helper column for me to convert the negative value to a positive as it should be but my formulas seem to just make it a negative value all the time!
I need it to show the value (not a negative), or in the event that it genuinely is a negative, then show "Not enough".
I can get it to work to convert it from a negative using column N, and I can get it to show not enough using the formula in column O, but I can't get them to work together in the same formula
Hopefully you will understand all that! :-)
Kind Regards,

[JeteMc]

If I understand correctly:
Paste the following into cell N5 and copy down: =IF(I5="","",IF(AND(I5<J5,I5-K5<>0,L5=0),"not enough",IF(I5<J5,I5-K5,0)))
If that is not correct then I suggest manually typing the values you would like to see into any cell in column N that is displaying an incorrect value.
Let us know if you have any questions.

[Johnny247]

Thanks for that. It works on some, but not all of them unfortunately.
I have attached the result for you to see, and the expected outcome is written in column O and highlighted in yellow.

Many thanks in advance.

[Mrrrr]

For example in cell N10 you say it should read "Not Enough" but as the formula is put, it shouldn't. Because the formula says:
=IF(I10="";"";IF(AND(I10<J10;I10-K10<>0;L10=0);"Not Enough";IF(I10<J10;I10-K10;0)))

All the colored arguments must be true for it to show "Not Enough".
Blue is true because I10 = 8400 and J10 = 91800.
Green is true because I10 (8400) minus K10 (-54600) = 63000 and is <>0.
Red is false, because L10 is not = 0, L10 is 9800.

The same goes for N5, N6 and N15.

But stock is not enough when K15<0, so why not just say that instead of AND(I10<J10;I10-K10<>0;L10=0)?

If you change the formula the way I suggested above, it becomes:
=IF(I5="";"";IF(K5<0;"Not Enough";IF(I5<J5;I5-K5;0)))

And shows "Not Enough" near all your yellow marked cells.

Or if you want to also use external stock as stock, you could say K5+L5<0 so the formula would become:
=IF(I5="";"";IF(K5+L5<0;"Not Enough";IF(I5<J5;I5-K5;0)))

See attached with the last formula.

[Johnny247]

Hi Mrrrr.
That is a very comprehensive explanation, thank you, and the final formula works as it is meant to. Many thanks for your help.

[Johnny247]

Good morning,
apologies, after some additional testing this morning, there is still a condition that is not met, that I "think" may require an OR statement in it. I have added comments for the condition and highlighted it in yellow. Please see attached.
Many thanks in advance.

[JeteMc]

It seems to me that the formula already in column K is taking care of the difference between columns H and I.
That being the case it seems that the formula in column N could just look at the difference between columns K and L using:

Please Login or Register  to view this content.

Let us know if you have any questions.

[Johnny247]

That seems to have fixed it.
Thank you very much for the help!
Kind Regards,

[JeteMc]

You're Welcome. Thank You for the feedback and for marking the thread as 'Solved'. I hope that you have a blessed day.