[Steve A]

i am putting together a spreadsheet for a new business i have launched this week. I am trying to do an if sum statement.

i have succeeded in doing a very a complicated nested if statement and it works well.

however i am banging my head against a problem, probably quite simple but can someone help.

i want a statement that allows me to do the following:

if the sum of a certain cell range ie b20 to f20 < 300 then multiply by .5 if it is greater than or equal to 300 but is less than 500 multipy by 1. and if it is greater than or equal to then 500 multiply by 2.

Any help this morning would be really appreciated.

Steve.

[Dave_3]

Wee bit of time since I did much Excel programming.

But, record a macro of you doing basically what you want, select range, sum, result into single cell.

Find that Function in the Visual basic coding editor, and then just add to the code the if .. then .. else statements.

Call that function in your summation box on the main spreadsheet.

I say record a macro, as it will show you the syntax for selecting your cell range etc. Easier to start with that than a blank page.

Haven't programmed directly in Excel for a few years, presume it still uses VBscript ? I would use a Function rather than a Subroutine - Function will return a single value.

D.

[Cafe.Racer]

=IF(SUM($B20:$F20)<300,F20*0.5,IF(SUM($B20:$F20)>5 00,F20*2,F20*1))

assuming you want to multiply each of the cell contents.

Edit, oh and check the "greater than or equal to" bit, I couldn't be bothered.

[mistry]

=IF(A1<300, A1*0.5,IF(A1>499,A1*2, A1))

Where A1 is the sum of the cell range.

[foxy-367]

I've had a go the only thing I can think is to do have three columns each with formulae to cover the options i.e Column A has the value you want, Col B covers the If formula for >300, Col C >300 and Col D >500. Then when you put the value into A the other three will calculate and the the correct value will be shown in Column D. Now hide B and C (Select columns B and C right CLick and click on 'Hide'). This probably makes no sense but the attachment shows the formulae and should give a better indication. Hope this helps.

Foxy

[Steve A]

Originally Posted by Cafe.Racer

=IF(SUM($B20:$F20)<300,F20*0.5,IF(SUM($B20:$F20)>5 00,F20*2,F20*1))

assuming you want to multiply each of the cell contents.

Edit, oh and check the "greater than or equal to" bit, I couldn't be bothered.

Thanks lads.

that works however its only multiplying f20 and not the sum of b20:f20 which i cant figure out.

i cant use the other expamles as A1 in my spread sheed is not a total cell but a range of cells..

and dave thanks for the first response but that went striaght over my head and is now somewhere near the himalayas lol

[mistry]

replace A1 in my formula with SUM($B20:$F20)

[themetz]

IF FUNCTION

Returns one value if a condition you specify evaluates to TRUE and another value if it evaluates to FALSE.

Use IF to conduct conditional tests on values and formulas.

Syntax

IF(logical_test,value_if_true,value_if_false)

Logical_test is any value or expression that can be evaluated to TRUE or FALSE. For example, A10=100 is a logical expression; if the value in cell A10 is equal to 100, the expression evaluates to TRUE. Otherwise, the expression evaluates to FALSE. This argument can use any comparison calculation operator.

Value_if_true is the value that is returned if logical_test is TRUE. For example, if this argument is the text string "Within budget" and the logical_test argument evaluates to TRUE, then the IF function displays the text "Within budget". If logical_test is TRUE and value_if_true is blank, this argument returns 0 (zero). To display the word TRUE, use the logical value TRUE for this argument. Value_if_true can be another formula.

Value_if_false is the value that is returned if logical_test is FALSE. For example, if this argument is the text string "Over budget" and the logical_test argument evaluates to FALSE, then the IF function displays the text "Over budget". If logical_test is FALSE and value_if_false is omitted, (that is, after value_if_true, there is no comma), then the logical value FALSE is returned. If logical_test is FALSE and value_if_false is blank (that is, after value_if_true, there is a comma followed by the closing parenthesis), then the value 0 (zero) is returned. Value_if_false can be another formula.

Remarks

Up to seven IF functions can be nested as value_if_true and value_if_false arguments to construct more elaborate tests. See the following last example.


When the value_if_true and value_if_false arguments are evaluated, IF returns the value returned by those statements.


If any of the arguments to IF are arrays, every element of the array is evaluated when the IF statement is carried out.


Microsoft Excel provides additional functions that can be used to analyze your data based on a condition. For example, to count the number of occurrences of a string of text or a number within a range of cells, use the COUNTIF worksheet function. To calculate a sum based on a string of text or a number within a range, use the SUMIF worksheet function. Learn about calculating a value based on a condition.

Examples

On a budget sheet, cell A10 contains a formula to calculate the current budget. If the result of the formula in A10 is less than or equal to 100, then the following function displays "Within budget". Otherwise, the function displays "Over budget".

IF(A10<=100,"Within budget","Over budget")

In the following example, if the value in cell A10 is 100, then logical_test is TRUE, and the total value for the range B5:B15 is calculated. Otherwise, logical_test is FALSE, and empty text ("") is returned that blanks the cell that contains the IF function.

IF(A10=100,SUM(B5:B15),"")

Suppose an expense worksheet contains in B2:B4 the following data for "Actual Expenses" for January, February, and March: 1500, 500, 500. C2:C4 contains the following data for "Predicted Expenses" for the same periods: 900, 900, 925.

You can write a formula to check whether you are over budget for a particular month, generating text for a message with the following formulas:

IF(B2>C2,"Over Budget","OK") equals "Over Budget"

IF(B3>C3,"Over Budget","OK") equals "OK"

Suppose you want to assign letter grades to numbers referenced by the name AverageScore. See the following table.

If AverageScore is Then return
Greater than 89 A
From 80 to 89 B
From 70 to 79 C
From 60 to 69 D
Less than 60 F


You can use the following nested IF function:

IF(AverageScore>89,"A",IF(AverageScore>79,"B",
IF(AverageScore>69,"C",IF(AverageScore>59,"D","F") )))
In the preceding example, the second IF statement is also the value_if_false argument to the first IF statement. Similarly, the third IF statement is the value_if_false argument to the second IF statement. For example, if the first logical_test (Average>89) is TRUE, "A" is returned. If the first logical_test is FALSE, the second IF statement is evaluated, and so on.

[Steve A]

Originally Posted by mistry

replace A1 in my formula with SUM($B20:$F20)

TA VERY MUCH JOB DONE

[willhollin]

Is this new business teaching Excel?

[Steve A]

Originally Posted by willhollin

Is this new business teaching Excel?

yes it is to slightly balding perverted plumbers. Do you know any?

[foxy-367]

Originally Posted by themetz

IF FUNCTION

Returns one value if a condition you specify evaluates to TRUE and another value if it evaluates to FALSE.

Use IF to conduct conditional tests on values and formulas.

Syntax

IF(logical_test,value_if_true,value_if_false)

Logical_test is any value or expression that can be evaluated to TRUE or FALSE. For example, A10=100 is a logical expression; if the value in cell A10 is equal to 100, the expression evaluates to TRUE. Otherwise, the expression evaluates to FALSE. This argument can use any comparison calculation operator.

Value_if_true is the value that is returned if logical_test is TRUE. For example, if this argument is the text string "Within budget" and the logical_test argument evaluates to TRUE, then the IF function displays the text "Within budget". If logical_test is TRUE and value_if_true is blank, this argument returns 0 (zero). To display the word TRUE, use the logical value TRUE for this argument. Value_if_true can be another formula.

Value_if_false is the value that is returned if logical_test is FALSE. For example, if this argument is the text string "Over budget" and the logical_test argument evaluates to FALSE, then the IF function displays the text "Over budget". If logical_test is FALSE and value_if_false is omitted, (that is, after value_if_true, there is no comma), then the logical value FALSE is returned. If logical_test is FALSE and value_if_false is blank (that is, after value_if_true, there is a comma followed by the closing parenthesis), then the value 0 (zero) is returned. Value_if_false can be another formula.

Remarks

Up to seven IF functions can be nested as value_if_true and value_if_false arguments to construct more elaborate tests. See the following last example.


When the value_if_true and value_if_false arguments are evaluated, IF returns the value returned by those statements.


If any of the arguments to IF are arrays, every element of the array is evaluated when the IF statement is carried out.


Microsoft Excel provides additional functions that can be used to analyze your data based on a condition. For example, to count the number of occurrences of a string of text or a number within a range of cells, use the COUNTIF worksheet function. To calculate a sum based on a string of text or a number within a range, use the SUMIF worksheet function. Learn about calculating a value based on a condition.

Examples

On a budget sheet, cell A10 contains a formula to calculate the current budget. If the result of the formula in A10 is less than or equal to 100, then the following function displays "Within budget". Otherwise, the function displays "Over budget".

IF(A10<=100,"Within budget","Over budget")

In the following example, if the value in cell A10 is 100, then logical_test is TRUE, and the total value for the range B5:B15 is calculated. Otherwise, logical_test is FALSE, and empty text ("") is returned that blanks the cell that contains the IF function.

IF(A10=100,SUM(B5:B15),"")

Suppose an expense worksheet contains in B2:B4 the following data for "Actual Expenses" for January, February, and March: 1500, 500, 500. C2:C4 contains the following data for "Predicted Expenses" for the same periods: 900, 900, 925.

You can write a formula to check whether you are over budget for a particular month, generating text for a message with the following formulas:

IF(B2>C2,"Over Budget","OK") equals "Over Budget"

IF(B3>C3,"Over Budget","OK") equals "OK"

Suppose you want to assign letter grades to numbers referenced by the name AverageScore. See the following table.

If AverageScore is Then return
Greater than 89 A
From 80 to 89 B
From 70 to 79 C
From 60 to 69 D
Less than 60 F


You can use the following nested IF function:

IF(AverageScore>89,"A",IF(AverageScore>79,"B",
IF(AverageScore>69,"C",IF(AverageScore>59,"D","F") )))
In the preceding example, the second IF statement is also the value_if_false argument to the first IF statement. Similarly, the third IF statement is the value_if_false argument to the second IF statement. For example, if the first logical_test (Average>89) is TRUE, "A" is returned. If the first logical_test is FALSE, the second IF statement is evaluated, and so on.

[willhollin]

Originally Posted by Steve A

yes it is to slightly balding perverted plumbers. Do you know any?

No, I only mingle with heating engineers.

[MOB]

Great place to ask these kind of questions and get quick answers;

http://www.mrexcel.com/forum/index.php

[Steve A]

Originally Posted by willhollin

No, I only mingle with heating engineers.

a plumber is a plumber mate. heating engineer is a plumber that likes to keep warm.

[willhollin]

Originally Posted by Steve A

. heating engineer is a plumber that likes to keep warm.

But often fails in trying to do so.

[Cafe.Racer]

Originally Posted by Steve A

a plumber is a plumber mate. heating engineer is a plumber that likes to keep warm.

[NFS]

Originally Posted by themetz

IF FUNCTION

Returns one value if a condition you specify evaluates to TRUE and another value if it evaluates to FALSE.

Use IF to conduct conditional tests on values and formulas.

Syntax

IF(logical_test,value_if_true,value_if_false)

Logical_test is any value or expression that can be evaluated to TRUE or FALSE. For example, A10=100 is a logical expression; if the value in cell A10 is equal to 100, the expression evaluates to TRUE. Otherwise, the expression evaluates to FALSE. This argument can use any comparison calculation operator.

Value_if_true is the value that is returned if logical_test is TRUE. For example, if this argument is the text string "Within budget" and the logical_test argument evaluates to TRUE, then the IF function displays the text "Within budget". If logical_test is TRUE and value_if_true is blank, this argument returns 0 (zero). To display the word TRUE, use the logical value TRUE for this argument. Value_if_true can be another formula.

Value_if_false is the value that is returned if logical_test is FALSE. For example, if this argument is the text string "Over budget" and the logical_test argument evaluates to FALSE, then the IF function displays the text "Over budget". If logical_test is FALSE and value_if_false is omitted, (that is, after value_if_true, there is no comma), then the logical value FALSE is returned. If logical_test is FALSE and value_if_false is blank (that is, after value_if_true, there is a comma followed by the closing parenthesis), then the value 0 (zero) is returned. Value_if_false can be another formula.

Remarks

Up to seven IF functions can be nested as value_if_true and value_if_false arguments to construct more elaborate tests. See the following last example.


When the value_if_true and value_if_false arguments are evaluated, IF returns the value returned by those statements.


If any of the arguments to IF are arrays, every element of the array is evaluated when the IF statement is carried out.


Microsoft Excel provides additional functions that can be used to analyze your data based on a condition. For example, to count the number of occurrences of a string of text or a number within a range of cells, use the COUNTIF worksheet function. To calculate a sum based on a string of text or a number within a range, use the SUMIF worksheet function. Learn about calculating a value based on a condition.

Examples

On a budget sheet, cell A10 contains a formula to calculate the current budget. If the result of the formula in A10 is less than or equal to 100, then the following function displays "Within budget". Otherwise, the function displays "Over budget".

IF(A10<=100,"Within budget","Over budget")

In the following example, if the value in cell A10 is 100, then logical_test is TRUE, and the total value for the range B5:B15 is calculated. Otherwise, logical_test is FALSE, and empty text ("") is returned that blanks the cell that contains the IF function.

IF(A10=100,SUM(B5:B15),"")

Suppose an expense worksheet contains in B2:B4 the following data for "Actual Expenses" for January, February, and March: 1500, 500, 500. C2:C4 contains the following data for "Predicted Expenses" for the same periods: 900, 900, 925.

You can write a formula to check whether you are over budget for a particular month, generating text for a message with the following formulas:

IF(B2>C2,"Over Budget","OK") equals "Over Budget"

IF(B3>C3,"Over Budget","OK") equals "OK"

Suppose you want to assign letter grades to numbers referenced by the name AverageScore. See the following table.

If AverageScore is Then return
Greater than 89 A
From 80 to 89 B
From 70 to 79 C
From 60 to 69 D
Less than 60 F


You can use the following nested IF function:

IF(AverageScore>89,"A",IF(AverageScore>79,"B",
IF(AverageScore>69,"C",IF(AverageScore>59,"D","F") )))
In the preceding example, the second IF statement is also the value_if_false argument to the first IF statement. Similarly, the third IF statement is the value_if_false argument to the second IF statement. For example, if the first logical_test (Average>89) is TRUE, "A" is returned. If the first logical_test is FALSE, the second IF statement is evaluated, and so on.

Thanks. You've really cleared that up for me.

[themetz]

Originally Posted by NFS

Thanks. You've really cleared that up for me.

you're welcome!

()