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

 

[Dryver14]

=IF(AE4-SUM(O4:O11)
 
Nopesorry<?XML:NAMESPACE PREFIX = X4,AE4-SUM(O4 /><X4,AE4-SUM(O4:O11)+ROUND(Z4-(AE4-SUM(O4:O11)) p Y4,0)*Y4,AE4-SUM(O4:O11))[html]<></X4,AE4-SUM(O4:O11)+ROUND(Z4-(AE4-SUM(O4:O11))>

 

[Dryver14]

<?xml:namespace prefix = x4,ae4-sum(o4 /><x4,ae4-sum(o411)+round(z4-(ae4-sum(o411)) p y4,0)*y4,ae4-sum(o411))<>I give up</x4,ae4-sum(o411)+round(z4-(ae4-sum(o411))>

 

[lenze]

Dryver:
Put a space before the < sign in your formula or turn off html!!
lenze

 

[Dryver14]

=IF(AE4-SUM(O4:O11) "Less Than" X4,AE4-SUM(O4:O11)+ROUND(Z4-(AE4-SUM(O4:O11))/Y4,0)*Y4,AE4-SUM(O4:O11)) <?xml:namespace prefix = x4,ae4-sum(o4 /><x4,ae4-sum(o411)+round(z4-(ae4-sum(o411)) p y4,0)*y4,ae4-sum(o411))<></x4,ae4-sum(o411)+round(z4-(ae4-sum(o411))>

 

[Joe Galvan]

Thanks for trying guys... I gave it a try and it doesn't work... and it doesnt allow me to step into the formaula and edit it.

 

[NateO]

=IF(AE4-SUM(O4:O11) "Less Than" X4,AE4-SUM(O4:O11)+ROUND(Z4-(AE4-SUM(O4:O11))/Y4,0)*Y4,AE4-SUM(O4:O11)) <?xml:namespace prefix = x4,ae4-sum(o4 /><x4,ae4-sum(o411)+round(z4-(ae4-sum(o411)) p y4,0)*y4,ae4-sum(o411))<></x4,ae4-sum(o411)+round(z4-(ae4-sum(o411))>

This can't be like this, try:

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

<X4,AE4-SUM(O4:O11)+ROUND(Z4-(AE4-SUM(O4:O11)) p Y4,0)*Y4,AE4-SUM(O4:O11))[ PHP]<>
<X4,AE4-SUM(O4:O11)+ROUND(Z4-(AE4-SUM(O4:O11)) p Y4,0)*Y4,AE4-SUM(O4:O11))<><X4,AE4-SUM(O4:O11)+ROUND(Z4-(AE4-SUM(O4:O11)) p Y4,0)*Y4,AE4-SUM(O4:O11))<><X4,AE4-SUM(O4:O11)+ROUND(Z4-(AE4-SUM(O4:O11)) p Y4,0)*Y4,AE4-SUM(O4:O11))<><X4,AE4-SUM(O4:O11)+ROUND(Z4-(AE4-SUM(O4:O11)) p Y4,0)*Y4,AE4-SUM(O4:O11))<><X4,AE4-SUM(O4:O11)+ROUND(Z4-(AE4-SUM(O4:O11)) p < Y4,0)*Y4,AE4-SUM(O4:O11))></X4,AE4-SUM(O4:O11)+ROUND(Z4-(AE4-SUM(O4:O11))></X4,AE4-SUM(O4:O11)+ROUND(Z4-(AE4-SUM(O4:O11))></X4,AE4-SUM(O4:O11)+ROUND(Z4-(AE4-SUM(O4:O11))></X4,AE4-SUM(O4:O11)+ROUND(Z4-(AE4-SUM(O4:O11))></X4,AE4-SUM(O4:O11)+ROUND(Z4-(AE4-SUM(O4:O11))>
<X4,AE4-SUM(O4:O11)+ROUND(Z4-(AE4-SUM(O4:O11)) p Y4,0)*Y4,AE4-SUM(O4:O11))<><X4,AE4-SUM(O4:O11)+ROUND(Z4-(AE4-SUM(O4:O11)) p Y4,0)*Y4,AE4-SUM(O4:O11))<><X4,AE4-SUM(O4:O11)+ROUND(Z4-(AE4-SUM(O4:O11)) p Y4,0)*Y4,AE4-SUM(O4:O11))<><X4,AE4-SUM(O4:O11)+ROUND(Z4-(AE4-SUM(O4:O11)) p Y4,0)*Y4,AE4-SUM(O4:O11))<><X4,AE4-SUM(O4:O11)+ROUND(Z4-(AE4-SUM(O4:O11)) p Y4,0)*Y4,AE4-SUM(O4:O11))<><X4,AE4-SUM(O4:O11)+ROUND(Z4-(AE4-SUM(O4:O11)) p Y4,0)*Y4,AE4-SUM(O4:O11))<><X4,AE4-SUM(O4:O11)+ROUND(Z4-(AE4-SUM(O4:O11)) p Y4,0)*Y4,AE4-SUM(O4:O11))<><X4,AE4-SUM(O4:O11)+ROUND(Z4-(AE4-SUM(O4:O11)) p Y4,0)*Y4,AE4-SUM(O4:O11))<><X4,AE4-SUM(O4:O11)+ROUND(Z4-(AE4-SUM(O4:O11)) p < Y4,0)*Y4,AE4-SUM(O4:O11))><X4,AE4-SUM(O4:O11)+ROUND(Z4-(AE4-SUM(O4:O11)) X4,AE4-SUM(O4:O11)+ROUND(Z4-(AE4-SUM(O4:O11)) Y4,0)*Y4,AE4-SUM(O4:O11))?<></X4,AE4-SUM(O4:O11)+ROUND(Z4-(AE4-SUM(O4:O11))></X4,AE4-SUM(O4:O11)+ROUND(Z4-(AE4-SUM(O4:O11))></X4,AE4-SUM(O4:O11)+ROUND(Z4-(AE4-SUM(O4:O11))></X4,AE4-SUM(O4:O11)+ROUND(Z4-(AE4-SUM(O4:O11))>
<X4,AE4-SUM(O4:O11)+ROUND(Z4-(AE4-SUM(O4:O11)) p Y4,0)*Y4,AE4-SUM(O4:O11))<><X4,AE4-SUM(O4:O11)+ROUND(Z4-(AE4-SUM(O4:O11)) p Y4,0)*Y4,AE4-SUM(O4:O11))<><X4,AE4-SUM(O4:O11)+ROUND(Z4-(AE4-SUM(O4:O11)) p Y4,0)*Y4,AE4-SUM(O4:O11))<><X4,AE4-SUM(O4:O11)+ROUND(Z4-(AE4-SUM(O4:O11)) p Y4,0)*Y4,AE4-SUM(O4:O11))<><X4,AE4-SUM(O4:O11)+ROUND(Z4-(AE4-SUM(O4:O11)) p < Y4,0)*Y4,AE4-SUM(O4:O11))></X4,AE4-SUM(O4:O11)+ROUND(Z4-(AE4-SUM(O4:O11))></X4,AE4-SUM(O4:O11)+ROUND(Z4-(AE4-SUM(O4:O11))></X4,AE4-SUM(O4:O11)+ROUND(Z4-(AE4-SUM(O4:O11))></X4,AE4-SUM(O4:O11)+ROUND(Z4-(AE4-SUM(O4:O11))></X4,AE4-SUM(O4:O11)+ROUND(Z4-(AE4-SUM(O4:O11))>
<X4,AE4-SUM(O4:O11)+ROUND(Z4-(AE4-SUM(O4:O11)) p Y4,0)*Y4,AE4-SUM(O4:O11))<><X4,AE4-SUM(O4:O11)+ROUND(Z4-(AE4-SUM(O4:O11)) p Y4,0)*Y4,AE4-SUM(O4:O11))<><X4,AE4-SUM(O4:O11)+ROUND(Z4-(AE4-SUM(O4:O11)) p Y4,0)*Y4,AE4-SUM(O4:O11))<><X4,AE4-SUM(O4:O11)+ROUND(Z4-(AE4-SUM(O4:O11)) p Y4,0)*Y4,AE4-SUM(O4:O11))<><X4,AE4-SUM(O4:O11)+ROUND(Z4-(AE4-SUM(O4:O11)) p < Y4,0)*Y4,AE4-SUM(O4:O11))></X4,AE4-SUM(O4:O11)+ROUND(Z4-(AE4-SUM(O4:O11))></X4,AE4-SUM(O4:O11)+ROUND(Z4-(AE4-SUM(O4:O11))></X4,AE4-SUM(O4:O11)+ROUND(Z4-(AE4-SUM(O4:O11))></X4,AE4-SUM(O4:O11)+ROUND(Z4-(AE4-SUM(O4:O11))></X4,AE4-SUM(O4:O11)+ROUND(Z4-(AE4-SUM(O4:O11))>
<X4,AE4-SUM(O4:O11)+ROUND(Z4-(AE4-SUM(O4:O11)) p Y4,0)*Y4,AE4-SUM(O4:O11))<><X4,AE4-SUM(O4:O11)+ROUND(Z4-(AE4-SUM(O4:O11)) p Y4,0)*Y4,AE4-SUM(O4:O11))<><X4,AE4-SUM(O4:O11)+ROUND(Z4-(AE4-SUM(O4:O11)) p Y4,0)*Y4,AE4-SUM(O4:O11))<><X4,AE4-SUM(O4:O11)+ROUND(Z4-(AE4-SUM(O4:O11)) p Y4,0)*Y4,AE4-SUM(O4:O11))<><X4,AE4-SUM(O4:O11)+ROUND(Z4-(AE4-SUM(O4:O11)) p < Y4,0)*Y4,AE4-SUM(O4:O11))>*untested*</X4,AE4-SUM(O4:O11)+ROUND(Z4-(AE4-SUM(O4:O11))></X4,AE4-SUM(O4:O11)+ROUND(Z4-(AE4-SUM(O4:O11))></X4,AE4-SUM(O4:O11)+ROUND(Z4-(AE4-SUM(O4:O11))></X4,AE4-SUM(O4:O11)+ROUND(Z4-(AE4-SUM(O4:O11))></X4,AE4-SUM(O4:O11)+ROUND(Z4-(AE4-SUM(O4:O11))></X4,AE4-SUM(O4:O11)+ROUND(Z4-(AE4-SUM(O4:O11))></X4,AE4-SUM(O4:O11)+ROUND(Z4-(AE4-SUM(O4:O11))></X4,AE4-SUM(O4:O11)+ROUND(Z4-(AE4-SUM(O4:O11))></X4,AE4-SUM(O4:O11)+ROUND(Z4-(AE4-SUM(O4:O11))></X4,AE4-SUM(O4:O11)+ROUND(Z4-(AE4-SUM(O4:O11))></X4,AE4-SUM(O4:O11)+ROUND(Z4-(AE4-SUM(O4:O11))>
</X4,AE4-SUM(O4:O11)+ROUND(Z4-(AE4-SUM(O4:O11))>

 

[Joe Galvan]

I got it!!

=IF((AG4-SUM($Q$4:$Q$11))<$X4,AG4-SUM($Q$4:$Q$11)+(((ROUND(($Z4-((AG4-(SUM($Q$4:$Q$11)))))/$Y4,0)))*$Y4),AG4-SUM($Q$4:$Q$11))


**** parentheses... I kept playing with it. Went from an unrecognized formula to, there wasnt enough arguments... stuck in some parentheses and TA DA!!


thanks all for your help!

 

[Dryver14]

@Nate

I couldnt paste my formule with the < in it Nate hence me writing it I am at work so no Excel genie to post!!


There still looks to be a lot of perethsis in there Joe but I suppose if it works it works

 

[NateO]

Yeah you shouldn't need that many parens, something like this:

=IF(AG4-SUM($Q$4:$Q$11)<$X4,AG4-SUM($Q$4:$Q$11)+ROUND($Z4-(AG4-SUM($Q$4:$Q$11))/$Y4,0)*$Y4,AG4-SUM($Q$4:$Q$11))

*untested*

I apologize, I'm more looking at this interface than the exact Q&A, right now.