Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Need a "simple" excel formula

Posted on 2013-01-10
20
Medium Priority
?
296 Views
Last Modified: 2013-01-14
Hi

At a client and just found I need this for an Excel 2007 formula:

If column H2 is Company One and Column F2 is  Greater than 1900, then Multiply Column A2 by 15 percent.

Thanks!
0
Comment
Question by:Rowby Goren
[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
  • 10
  • 7
  • 2
  • +1
20 Comments
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 38764670
=IF(AND(H2="Company One",F2>1900),A2*.15,A2)

Kevin
0
 
LVL 41

Expert Comment

by:Kyle Abrahams
ID: 38764682
This assumes you want A2 if the condition doesn't match

=IF(H2="Company One", IF(F2 > 1900, A2 * 1.15, A2), A2)
0
 
LVL 9

Author Comment

by:Rowby Goren
ID: 38765116
Hi,

Thanks for helping!  

Both formulas are close, but not quite working the way I intended.  I guess I should have put it as a description instead of just a "formula".


ged325's comes the closest.  

The formula you wrote is increasing all "company one", not just those over 1900.  

Please fix or ask for clarification.

Thanks!

Rowby
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 38765161
Fixed:

=IF(AND(H2="Company One",F2>1900),A2*1.15,A2)

Kevin
0
 
LVL 9

Author Comment

by:Rowby Goren
ID: 38765350
Hi Kevin (and ged325)

New formula is still not working quite right.

See attached sample spreadsheet (with actual column names)

Hopefully that will help.
expert-exchamge.xls
0
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 38765427
The formula works perfectly. I have added it to the workbook in column I. Since there are no companies named "Company One" I changed the formula to look for "MERCURY STANDARD HO".

=IF(AND(H2="MERCURY STANDARD HO",F2>1900),A2*1.15,A2)

Another possible problem is that you are testing for a year greater than 1900. All the years are greater then 1900 so the formula will always add 15% to the value in column A.

Kevin
expert-exchamge.xls
0
 
LVL 9

Author Comment

by:Rowby Goren
ID: 38765438
Hi  

I should clarify.  The 1900 is not a year.  Just a number -- specifically square footage.

Does that help.

Rowby
0
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 38765442
Then why does the header say "Year Built"?

Kevin
0
 
LVL 9

Author Comment

by:Rowby Goren
ID: 38765459
Ooops.  I am happy I included an example spreadsheet.

The 1900  item in the formula refers to column "G" not column "F"

So sorry!

Rowby
0
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 38765462
Then use this formula:

=IF(AND(H2="MERCURY STANDARD HO",G2>1900),A2*1.15,A2)

Kevin
0
 
LVL 9

Author Comment

by:Rowby Goren
ID: 38765480
Hi Kevin

=IF(AND(H2="MERCURY STANDARD HO",G2>1900),A2*1.15,A2)

The formula is increasing all of MErcury -- even if it is under or over 1900 square feet.

Can you put you formula in that sample spreadsheet I uploaded, and post it like your did before.  Perhaps we are doing something wrong...

Thanks!

Rowby
0
 
LVL 9

Author Comment

by:Rowby Goren
ID: 38765667
Hi all,

Looking for an update from Kevin.   OR anyone else.  If you could put your solution in my sample excel sheet, that would be great.

Thanks

Rowby
0
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 38765712
Here you go.

Kevin
expert-exchamge.xls
0
 
LVL 9

Author Comment

by:Rowby Goren
ID: 38765752
Thanks, Kevin,

I sent it to my friiend and he will look at it tonight or first thing in morning.

Appreciate your ongoing troubleshooting on this :)

Rowby
0
 
LVL 9

Author Comment

by:Rowby Goren
ID: 38767740
Hi

The best thing Kevin, would be a simpler version

1 IF in columnI "Mercury Standard Homeowners" then take column a and multiply it by 1.15.

We are ignoring the square footage.

In column A: there are many companies listed, but we only want Mercury Standard Homowers HO" to be multiplied by 1.15.   We want all the other companies to be left alone, no changes.

Thanks!
0
 
LVL 81

Accepted Solution

by:
zorvek (Kevin Jones) earned 1600 total points
ID: 38768294
Then use this formula:

=IF(H2="MERCURY STANDARD HO",A2*1.15,A2)

Kevin
0
 
LVL 33

Assisted Solution

by:Rob Henson
Rob Henson earned 400 total points
ID: 38768938
For future flexibility it may be worth using a MATCH function and a list of options.

Create list of companies that will require the change (in separate sheet if so required) and give range name eg LIST

Then the formula would be:

=IF(ISERROR(MATCH(H2,LIST,0)),A2,A2*1.15)

By hard coding the company name in the formula you are relying on an exact match against one variable, having a list expands the variables but still need an exact match.

For consistency you could use Data Validation against a full list to populate the company names in the sheet. You could then use the same DV criteria to populate the shortened list.

You could expand further with variable % changes for the different companies; list the required % rate in the next column for each company that will change and rather than hard coding the % rate have a lookup to find it in the list. This list would then have to be both columns so would need a separate range name eg LIST2.

=IF(ISERROR(MATCH(H2,LIST,0)),A2,A2*VLOOKUP(H2,LIST2,2,FALSE))

Going further still and combining those ideas, have a complete list of company names and against those that need changing have the required rate or just 1 against those that need no change. LIST (complete list, one column just names) could still be used for Data Validation. LIST2 would again be complete list and % column. Then the formula would be:

=A2*VLOOKUP(H2,LIST2,2,FALSE)

If you brought back in the square footage requirement it would get more complicated but not impossible.

Thanks
Rob H
0
 
LVL 33

Expert Comment

by:Rob Henson
ID: 38768974
See attached with variable rates for square footage as well.

Apologies if this goes beyond your request for "simple"!!

Thanks
Rob H
expert-exchange.xls
0
 
LVL 9

Author Comment

by:Rowby Goren
ID: 38769072
Hi Rob -- fascination and informative suggestion.  I will review it and take what I can from it.

Stay tuned everyone. Will finish this up on Monday.

Rowby
0
 
LVL 9

Author Closing Comment

by:Rowby Goren
ID: 38775895
Thanks Kevin -- that worked.  Appreciate you taking the time to troubleshoot it for me.

And thanks Rob too -- I will dig into that link and example.

Rowby
0

Featured Post

What’s Wrong with Your Cloud Strategy ?

Even as many CIOs are embracing a cloud-first strategy, the reality is that moving to the cloud is a lengthy process and the end-state is likely to be a blend of multiple clouds—public and private. Learn why multicloud solutions matter in this webinar by Nimble Storage.

Question has a verified solution.

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

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

650 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