• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 303
  • Last Modified:

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!
0
Rowby Goren
Asked:
Rowby Goren
  • 10
  • 7
  • 2
  • +1
2 Solutions
 
zorvek (Kevin Jones)ConsultantCommented:
=IF(AND(H2="Company One",F2>1900),A2*.15,A2)

Kevin
0
 
Kyle AbrahamsSenior .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
 
Rowby GorenAuthor 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.  

Please fix or ask for clarification.

Thanks!

Rowby
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
zorvek (Kevin Jones)ConsultantCommented:
Fixed:

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

Kevin
0
 
Rowby GorenAuthor 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
 
zorvek (Kevin Jones)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
 
Rowby GorenAuthor Commented:
Hi  

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

Does that help.

Rowby
0
 
zorvek (Kevin Jones)ConsultantCommented:
Then why does the header say "Year Built"?

Kevin
0
 
Rowby GorenAuthor 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
 
zorvek (Kevin Jones)ConsultantCommented:
Then use this formula:

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

Kevin
0
 
Rowby GorenAuthor 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
 
Rowby GorenAuthor 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
 
zorvek (Kevin Jones)ConsultantCommented:
Here you go.

Kevin
expert-exchamge.xls
0
 
Rowby GorenAuthor 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
 
Rowby GorenAuthor 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
 
zorvek (Kevin Jones)ConsultantCommented:
Then use this formula:

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

Kevin
0
 
Rob HensonIT & Database AssistantCommented:
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
 
Rob HensonIT & Database AssistantCommented:
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
 
Rowby GorenAuthor 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
 
Rowby GorenAuthor 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

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

  • 10
  • 7
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now