Formula Help Please

[Joe Galvan]

=IF(AE4-((Sum((O$4:O$11))<$X4,AE4-((Sum((O$4:O$11)))+(((ROUND(($Z4-(AE4-((Sum((O$4:O$11)))/$Y4,0)))*$Y4),AE4-((Sum((O$4:O$11)))


The underlined parts in the formula used to reference just one cell... now i need to to sum a range first, then complete the rest of the function.

Not sure if sticking a Sum function in the middle of the formula was the right thing to do....

Any help is greatly appreciated.



thanks,
JOE

 

[lenze]

The big question is, does it give you the expected results???

lenze

 

[Joe Galvan]

The above formula doesnt... when the Sum part was just referencing one cell it did...

 

[lenze]

What is your original formula and can you give some sample data??
lenze

 

[Joe Galvan]

Original Formula
=IF((AE4-$AC4)<$X4,AE4-$AC4+(((ROUND(($Z4-(AE4-$AC4))/$Y4,0)))*$Y4),AE4-$AC4)


The formula is for a forecasting model... basically it takes demand, re-order points/quantites, and current inventory and tells you when you will need to produce more product.

AE= Inventory Position
AC=weekly deman
X=Re-Order Point
Z=Re-Order point and Quantity combined
Y= Re-Order Quantity

AE=11,060
AC=500
X=250
Z=1,250
Y= 1,000


So the formula removes the demand, while making sure that it hasnt dropped below the re-order point... once it does drop, it put the re-order quantity into inventory as if you produced that product.. and starts all over again.


Sorry I cannot upload anything from the business computer...


thanks,
JOE

 

[lenze]

Is there a Formula (SUM??) in AC??
lenze

 

[Dryver14]

This sorts out the sum bit but I'm not sure it returns what you want

=IF(AE4-SUM(O4:O11)<X4,AE4-SUM(O4:O11)+ROUND(Z4-(AE4-SUM(O4:O11))/Y4,0)*Y4,AE4-SUM(O4:O11))

 

[Joe Galvan]

Cell 'AC', needs to become a SUM formula... i need it to sum a range first, before, the range is O4-O11


normally I would just sum it and continue using the one cell reference... but Im working within the confinds of a formatted, established workbook...

 

[Dryver14]

not sure whats going on it will not let me post the full formula...one more go

=IF(AE4-SUM(O4:O11)<X4,AE4-SUM(O4:O11)+ROUND(Z4-(AE4-SUM(O4:O11))/Y4,0)*Y4,AE4-SUM(O4:O11))

 

[lenze]

Use the "Evaluate Formula" tool to see where it it going wrong!!!
II don't see why it shouldn't work!!
lenze