Setting Conditional Formulas
 

I have 4 variables with value of either 0 or 1 - Var1, Var2, Var3, Var
I have 4 cells that correspond to those variables - "Cell1", "Cell2", "Cell3", "Cell4
And I have a total cell - "Total

I am trying to set the formula of "Total" to add all cells that the variable is 1

e.g. If Var1, Var3 and Var4 are equal to 1 then
Range("Total").Value = "=Cell1+Cell3+Cell4

But I don't know how to work the IF statement into setting the formula
Any Ideas? (Besides writing a whole lot of nested IF statements

Thanks
Wes

Setting Conditional Formulas
 

Since var1, var2, var3, var4 are all 0/1, couldn't you just add them. The 0's
won't hurt.

range("total").value = var1 + var2 + var3 + var4

oohhhh.

Var1...var4 are 0/1's, but the cell1...cell4 don't have to be:

range("total").value = var1 * range("cell1").value _
+ var2 * range("cell2").value _
+ var3 * range("cell3").value _
+ var4 * range("cell4").value


0*whatever won't hurt the total. (as long as those cells are numeric!)



Wstohler wrote:

I have 4 variables with value of either 0 or 1 - Var1, Var2, Var3, Var4
I have 4 cells that correspond to those variables - "Cell1", "Cell2", "Cell3", "Cell4"
And I have a total cell - "Total"

I am trying to set the formula of "Total" to add all cells that the variable is 1.

e.g. If Var1, Var3 and Var4 are equal to 1 then
Range("Total").Value = "=Cell1+Cell3+Cell4"

But I don't know how to work the IF statement into setting the formula.
Any Ideas? (Besides writing a whole lot of nested IF statements)

Thanks,
Wes

--

Dave Peterson

Setting Conditional Formulas
 

if you want to do something like this in a worksheet

assume A1:A4 have 0 or 1
assume B1:B4 have numeric entries

=sumproduct(A1:A4,B1:B4)

--
Regards,
Tom Ogilvy


Wstohler wrote in message
...
I have 4 variables with value of either 0 or 1 - Var1, Var2, Var3, Var4
I have 4 cells that correspond to those variables - "Cell1", "Cell2",
"Cell3", "Cell4"
And I have a total cell - "Total"

I am trying to set the formula of "Total" to add all cells that the
variable is 1.

e.g. If Var1, Var3 and Var4 are equal to 1 then
Range("Total").Value = "=Cell1+Cell3+Cell4"

But I don't know how to work the IF statement into setting the formula.
Any Ideas? (Besides writing a whole lot of nested IF statements)

Thanks,
Wes

Setting Conditional Formulas
 

Thanks for the help,
The problem is that I'm using named ranges for Cell1, etc. And some might be deleted. So if I try to add a named range that has already been deleted, it will give me an error.

Any further ideas?

Setting Conditional Formulas
 

Tot = 0
for i = 1 to 4
set rng = nothing
on Error Resume Next
set rng = Range("Cell" & i)
on Error goto 0
if not rng is nothing then
tot = tot + rng * choose(i,var1,var2,var3,var4)
end if
Next

--
Regards,
Tom Ogilvy

"Wstohler" wrote in message
...
Thanks for the help,
The problem is that I'm using named ranges for Cell1, etc. And some might
be deleted. So if I try to add a named range that has already been deleted,
it will give me an error.

Any further ideas?