Solved Help with IF Function

October 18, 2012 at 12:51:28
Specs: Windows XP
Need help adding a function to a formula in Excel 2010.
Formula is located in D25.
Current Formula: =IF(OR($G25>0,$H25>0,$I25>0,$J25>0,$K25>0,$L25>0,$M25>0),SUM($I$10-$P25), SUM($I$10))

Want to add IF D25 IS LESS THAN ZERO THEN DEDUCT FROM VALUE OF C25, then will copy the formula with respective changes being IF C25 IS LESS THAN ZERO THEN DEDUCT FROM VALUE OF D25.


See More: Help with IF Function

Report •


#1
October 18, 2012 at 22:11:09
✔ Best Answer
"Want to add IF D25 IS LESS THAN ZERO THEN DEDUCT FROM VALUE OF C25, then will copy the formula with respective changes being IF C25 IS LESS THAN ZERO THEN DEDUCT FROM VALUE OF D25.

I'm confused as you are asking to perform calculations using a formula that references it's own cell location. This is a circular reference and cannot be done.

We can move the additions that you are requesting for this formula to a new cell, however you have not provided information as to what order you would like formula calculations to take place.

Please let me know the exact order that you want the steps of the formula to be performed in. For example:

- First, check to see if $G25, $H25, $I25, $J25, $K25, $L25, or $M25 are greater than 0, if so then SUM $I$10-$P25, if not, then display $I$10 (the SUM function for this part in the original formula is not needed as there is only 1 cell being referenced.
- Next, check to see if D25 is a negative number, if so then calculate C25-D25.
- Finally, check to see if C25 is a negative number, if so, then calculate D25-C25.


EDIT: I think I know what you are trying to do here. It's not the cleanest formula in the world, but if you have to fit everything into 1 cell and cannot use another row/column/sheet for hidden formulas, then this will have to do.

=IF(IF(OR($G25>0,$H25>0,$I25>0,$J25>0,$K25>0,$L25>0,$M25>0),
SUM($I$10-$P25), $I$10)<0,
IF(OR($G25>0,$H25>0,$I25>0,$J25>0,$K25>0,$L25>0,$M25>0),
SUM($I$10-$P25), $I$10)+C25,
IF(C25<0,C25+IF(OR($G25>0,$H25>0,$I25>0,$J25>0,$K25>0,$L25>0,$M25>0),
SUM($I$10-$P25), $I$10),
IF(OR($G25>0,$H25>0,$I25>0,$J25>0,$K25>0,$L25>0,$M25>0),
SUM($I$10-$P25), $I$10)))

Try that out and let me know if we need to tweak it a little.

-----
IT Desktop & Network Consultant - MOS Master Certified, MCP, MCSA, MCITP - Windows 7, CCNA Certificate Pending, A+, Network +

::geek::

edited by moderator: Formatted to fit viewing pane


Report •

#2
October 19, 2012 at 03:57:58
When posting in a Help forum such as this, please try to use a Subject Line that tells us something about your question. If everyone used a generic Subject Line such as "I Need Help" we wouldn't be able to tell one question from another and the Archives would essentially be useless.

I have edited the subject line of your post to show you what I mean. Scan the list of questions in this forum to see the types of Subject Lines that others have used.

In addition, please do not use upper case letters when telling us hat you need. Upper case letters is the Internet equivalent of shouting, and no one likes to be shouted at.

Thanks!

DerbyDad03
Office Forum Moderator

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Related Solutions


Ask Question