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

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

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

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?

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?