Solved

IF statement using AND in Excel 2010

Posted on 2014-03-15
6
336 Views
Last Modified: 2014-03-15
I am very frustrated with the syntax needed in Excel 2010 to perform a simple IF AND statement. The following formula works flawlessly if K4>0.

=IF(K4>0,(O27 + (E13/2) + E25),(O27 + (E13/2) + E25 + P2))

I need to add the logic to make the final result be 0 if K4 =0.
Any and all help will be greatly appreciated.
0
Comment
Question by:cfrake
  • 3
  • 2
6 Comments
 
LVL 48

Expert Comment

by:dbrunton
ID: 39932137
Try

=IF(K4>0,(O27 + (E13/2) + E25), IF(K4=0, 0,(O27 + (E13/2) + E25 + P2)))

and

=IF(K4>0,O27 + (E13/2) + E25,IF(K4=0, 0,O27 + (E13/2) + E25 + P2))

might do it as well.  I've taken out brackets that may not be necessary.
0
 

Author Comment

by:cfrake
ID: 39932176
Thank You,
I think my question was poorly presented.
The AND has to do with if P2 is added or not and is based on another cell (N4).
Pseudo code:
If K4 >0 AND N4<3 Then (O27 + (E13/2) + E25)
ELSE IF K4>0 AND N4=3 then (O27 + (E13/2) + E25 + P2
ELSE 0.

All cells other than K4 are always populated and the original formula is good unless K4=0.
Because all other cells are populated the result is always greater than 0.
If K4=0 the end result needs to be 0.
Thank you again for the response and i hope that clarifies the question.
0
 
LVL 48

Accepted Solution

by:
dbrunton earned 500 total points
ID: 39932194
Try this

=if(and(k4>0,n4<3),O27+(E13/2)+E25,if(and(k4>0,n4=3),O27+(E13/2)+E25+p2,0))
0
Flexible connectivity for any environment

The KE6900 series can extend and deploy computers with high definition displays across multiple stations in a variety of applications that suit any environment. Expand computer use to stations across multiple rooms with dynamic access.

 
LVL 41

Expert Comment

by:Sharath
ID: 39932198
try this.

=IF(K4>0, IF(N4<= 3, O27 + (E13/2) + E25,0) + IF(N4=3,P2,0),0)
0
 
LVL 48

Expert Comment

by:dbrunton
ID: 39932201
Probably this one

=if(k4=0,0,if(and(k4>0,n4<3),O27+(E13/2)+E25,if(and(k4>0,n4=3),O27+(E13/2)+E25+p2,0)))

Note that if k4 >0 and n4>3 it will also return 0.
0
 

Author Closing Comment

by:cfrake
ID: 39932205
PERFECT!!
 I will have to tweak some cell references finish up the project now but you have provided me with the syntax required.
I cannot thank you enough.
I wish I would have posted this question here before wasting all that time hammering at it on my own.
Thank you,
0

Featured Post

SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Article by: Leon
Software Metering within our group of companies has always been an afterthought until auditing of software and licensing became a pain point. Orchestrator and SCCM metering gave us the answer and it was an exciting process.
Meetings to discuss business process can waste time, and often do .  The meeting's dialog can get confusing when participants have different professional perspectives and backgrounds.  A jointly-developed process picture helps wade through the confu…
This video shows where to find templates, what they are used for, and how to create and save a custom template using Microsoft Word.
An overview on how to enroll an hourly employee into the employee database and how to give them access into the clock in terminal.

730 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question