Combining Formulas

Spencer25

Board Regular
Joined
May 3, 2006
Messages
76
Hi everyone,
Thanks in advance for looking at this, here is a quick run down.

I have a spreadsheet that monitors production units required for a given week in Column "F".

The next column "G" has what was actually produced in it.

The next column "H" has the difference between the two. If the amount in Column G is less than or equal to 95% of the required production "F", the shortage is recorded in column "I" ( it will be added to the next weeks upcoming production (manually)- please see formula below.

This upcoming weeks order is pulled into column "J"

If the amount in Column G is greater than or equal to 110% of the required production "F", the overage is recorded in column "K" ( it will be deducted from the next weeks upcoming production (manually)- please see formula below.

I would like to combine the two criteria into one column if possible.


Formula in cell "I"
=IF(H9>0,"",(IF((H9*-1)>0.04999*F9,-H9,"")))


Formula in cell "K"
=IF($B9="","",IF(H9<0,"",(IF(H9>0.09999*F9,-H9,""))))
 

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

texasalynn

Well-known Member
Joined
May 19, 2002
Messages
8,458
is this what you want?
Code:
=IF(H9>0,"",(IF((H9*-1)>0.04999*F9,-H9,IF($B9="","",IF(H9<0,"",(IF(H9>0.09999*F9,-H9,"")))))))
 

Spencer25

Board Regular
Joined
May 3, 2006
Messages
76
Hi, no this won't work because of the conflicting H<0 and H>0 I think. Also, I don't think I should have to use .049999 etc. there should be a less than or equal to function, correct? Thanks again
 

barry houdini

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
What's the formula in H9?

Assuming you want a positive value for an "overage" and negative for a shortage try this formula

=IF(B9="","",IF(OR(F9/G9<=0.95,F9/G9>=1.1),G9-F9,""))

You can adjust it if you need always want positive values
 

Forum statistics

Threads
1,140,916
Messages
5,703,158
Members
421,278
Latest member
16cwilliams

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top