IF statement problem

[Paul Mcfarling]

Hi Everyone,

I am trying to formulate the following for my sales team. I have been using an IF statement ( =IF(D10>=3000,"200",IF(D10>=2999,"150",IF(D10>=2600,"100",IF(D10>=100,"100"))))

but I can only get the the calculation to work with 1 and 2. I do not know how to put a range of numbers in.

1. If they get an average sales of up to £2599 then they receive £100 bonus payment
2. If they get an average sale of between £2600 to £2999 they receive £150
3. If the average sale is over £3000 then they will receive £200

Any help for a novice would be appreciated.

 

[gaz_chops]

Try
=IF(D10<=2599,100,IF(AND(D10>=2600,D10<=2999),150,IF(D10>=3000,200,"")))

 

[Paul Mcfarling]

Try
=IF(D10<=2599,100,IF(AND(D10>=2600,D10<=2999),150,IF(D10>=3000,200,"")))

You are a star.... It works brilliantly thanks you so much.

 

[gaz_chops]

You're welcome

 

[architechnid]

I'm trying to do something similar. I want to be able to insert the value of a title insurance fee based on a purchase price. The state of Texas, in all their wisdom, decided that instead of making this a simple percentage formula, or even a few of these, they would just give you the price in a table. For a purchase price of 10000 or less, the fee is 238. For a purchase price of 10500 or less, but above 10,000, the fee is 242, and so on every $500 up to $100,000, then it increases every $1,000, then $5000, etc.

I have a two column list. In column A are the purchase price values. In Column B are the fee values. I want a formula that says 'Look at the purchase price value (cell G3). If G3's value is between the values for A1 and A2, then show the value in B1; If G3's value is between the values for A2 and A3, then show the value in B2; etc. through 307 rows.

Thanks in advance. I just can't get my head wrapped around how the IF,AND, OR, and BUT formulas work...

 

[gaz_chops]

Hi, try Index/Match
=INDEX($C$1:$C$2,MATCH(F6,$B$1:$B$2,1))
$C$1:$C$2 will be the range of fees
F6 is the value you want to look for
$B$1:$B$2 is the range you want to look at

 

[bbott]

Hi Everyone,

I am trying to formulate the following for my sales team. I have been using an IF statement ( =IF(D10>=3000,"200",IF(D10>=2999,"150",IF(D10>=2600,"100",IF(D10>=100,"100"))))

but I can only get the the calculation to work with 1 and 2. I do not know how to put a range of numbers in.

1. If they get an average sales of up to £2599 then they receive £100 bonus payment
2. If they get an average sale of between £2600 to £2999 they receive £150
3. If the average sale is over £3000 then they will receive £200

Any help for a novice would be appreciated.

Another option for this:

=LOOKUP(D10,{0,2600,3000},{100,150,200})

I find that it's easier to add on to a formula like this rather than adding additional nested ifs. Just my two cents...

 

[architechnid]

Hi, try Index/Match
=INDEX($C$1:$C$2,MATCH(F6,$B$1:$B$2,1))
$C$1:$C$2 will be the range of fees
F6 is the value you want to look for
$B$1:$B$2 is the range you want to look at

Yeah, that worked like a boss. Your help is immensely appreciated!

 

[architechnid]

Okay, so here's another one:

I have a list of deposits and expenses. I have a list of categories (i.e.: Loan Advance, Deposit, Change order, One time expense, etc.). Row B is the memo, row c the category, row f are expenses, and row g are deposits. I'd like to have a report that provides the total amount (deposit or withdrawal) for a given category.

The best way I figured was to do an index within a sum function, but that's not working too great...

Also: What do the dollar signs indicate in the index function?

 

[gaz_chops]

The $ fixes the the cell, so when you copy the formula it stays fixed.

Have you tried a pivot table for your report?