# Need a "simple" excel formula

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!
LVL 9
###### Who is Participating?

ConsultantCommented:
Then use this formula:

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

Kevin
0

ConsultantCommented:
=IF(AND(H2="Company One",F2>1900),A2*.15,A2)

Kevin
0

Senior .Net DeveloperCommented:
This assumes you want A2 if the condition doesn't match

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

Author Commented:
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.

Thanks!

Rowby
0

ConsultantCommented:
Fixed:

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

Kevin
0

Author Commented:
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

ConsultantCommented:
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

Author Commented:
Hi

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

Does that help.

Rowby
0

ConsultantCommented:
Then why does the header say "Year Built"?

Kevin
0

Author Commented:
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

ConsultantCommented:
Then use this formula:

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

Kevin
0

Author Commented:
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

Author Commented:
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

ConsultantCommented:
Here you go.

Kevin
expert-exchamge.xls
0

Author Commented:
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

Author Commented:
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

Finance AnalystCommented:
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

Finance AnalystCommented:
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

Author Commented:
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

Author Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.