Solved

Excel Formula

Posted on 2011-02-19
3
298 Views
Last Modified: 2012-05-11
Hello trying to build a formula to do the following
If cell P5 contains "PL" anywhere in that cell then take O5*.5, else if P5 does not contain PL, but there is a 'SN" and AM5 is greater than 10 and  I5 is less than 1.25 then O5*15, else O5*10. if niether PL or SN then 0.
0
Comment
Question by:sandramac
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
3 Comments
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 34933973
Try this

=IF(ISERROR(IF(FIND("PL",P5,1),O5*0.5,IF(FIND("SN",P5,1),IF(AND(AM5>10,I5<1.25),O5*15,O5*10),0))),0,IF(FIND("PL",P5,1),O5*0.5,IF(FIND("SN",P5,1),IF(AND(AM5>10,I5<1.25),O5*15,O5*10),0)))

Sid
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 34933979
The above formula is for Excel 2003. For 2007/2010 try this

=IFERROR(IF(FIND("PL",P5,1),O5*0.5,IF(FIND("SN",P5,1),IF(AND(AM5>10,I5<1.25),O5*15,O5*10),0)),0)

Sid
0
 
LVL 30

Accepted Solution

by:
SiddharthRout earned 500 total points
ID: 34933990
Another alternative

=IF(ISNUMBER(FIND("PL",P5,1)),O5*0.5,IF(ISNUMBER(FIND("SN",P5,1)),IF(AND(AM5>10,I5<1.25),O5*15,O5*10),0))

Sid
0

Featured Post

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

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

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

623 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