Solved

Need a "simple" excel formula

Posted on 2013-01-10
20
247 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 39

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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
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 31

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 31

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

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
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 …
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

758 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

18 Experts available now in Live!

Get 1:1 Help Now