Solved

Need a "simple" excel formula

Posted on 2013-01-10
20
256 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
  • 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 40

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
 
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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
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 400 total points
ID: 38768294
Then use this formula:

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

Kevin
0
 
LVL 32

Assisted Solution

by:Rob Henson
Rob Henson earned 100 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 32

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

910 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now