I'm trying to calculate percentages that a column of values are of a
single cell value, e.g.
column a contains values 10 through 100
column b will contain the percentage that a is of a cell C6
I'm trying to populate the cell formulas in column B with an =IF(...
formula, is that possible, I tried this

for n = 1 to 10
Cells(n, 2).Formula = "=IF(C6, Cells(n, 1).Value / cells(6,3).value &
, 0)"
next

But that won't work, I've tried a few other things, I want the sheet to
update when different values are entered into C6, with the percentages
if C6 is not empty, and with 0's if c6 is empty.

Does anyone have any suggestions?

TIA,
Mike

hi,

i believe that the condition of "IF" is missing
=IF(C6 <<what,$A$1 /$C$6, 0)

For n = 1 To 10
Cells(n, 2).Formula = "=IF(C6," & Cells(n, 1).Address & " /" & Cells(6,
3).Address & ", 0)"
Next

isabelle



Le 2016-05-19 17:19, Mike S a écrit :
I'm trying to calculate percentages that a column of values are of a single cell
value, e.g.
column a contains values 10 through 100
column b will contain the percentage that a is of a cell C6
I'm trying to populate the cell formulas in column B with an =IF(... formula, is
that possible, I tried this

for n = 1 to 10
Cells(n, 2).Formula = "=IF(C6, Cells(n, 1).Value / cells(6,3).value & , 0)"
next

But that won't work, I've tried a few other things, I want the sheet to update
when different values are entered into C6, with the percentages if C6 is not
empty, and with 0's if c6 is empty.

Does anyone have any suggestions?

TIA,
Mike

maybe...

For n = 1 To 10
Cells(n, 2).Formula = "=IF(C6<""""," & Cells(n, 1).Address & " /" & Cells(6,
3).Address & ", 0)"
Next
isabelle

Le 2016-05-19 18:40, isabelle a écrit :
hi,

i believe that the condition of "IF" is missing
=IF(C6 <<what,$A$1 /$C$6, 0)

For n = 1 To 10
Cells(n, 2).Formula = "=IF(C6," & Cells(n, 1).Address & " /" & Cells(6,
3).Address & ", 0)"
Next

isabelle



Le 2016-05-19 17:19, Mike S a écrit :
I'm trying to calculate percentages that a column of values are of a single cell
value, e.g.
column a contains values 10 through 100
column b will contain the percentage that a is of a cell C6
I'm trying to populate the cell formulas in column B with an =IF(... formula, is
that possible, I tried this

for n = 1 to 10
Cells(n, 2).Formula = "=IF(C6, Cells(n, 1).Value / cells(6,3).value & , 0)"
next

But that won't work, I've tried a few other things, I want the sheet to update
when different values are entered into C6, with the percentages if C6 is not
empty, and with 0's if c6 is empty.

Does anyone have any suggestions?

TIA,
Mike

On 5/19/2016 4:22 PM, isabelle wrote:
maybe...
For n = 1 To 10
Cells(n, 2).Formula = "=IF(C6<""""," & Cells(n, 1).Address & " /" &
Cells(6, 3).Address & ", 0)"
Next
isabelle

This worked perfectly, thank you so much!

For n = 34 To 81
Cells(n, 4).Formula = "=IF(D31," & Cells(n, 2).Address & " /" &
Cells(31, 4).Address & ", 0)"
Next

Le 2016-05-19 18:40, isabelle a écrit :
hi,

i believe that the condition of "IF" is missing
=IF(C6 <<what,$A$1 /$C$6, 0)

For n = 1 To 10
Cells(n, 2).Formula = "=IF(C6," & Cells(n, 1).Address & " /" &
Cells(6,
3).Address & ", 0)"
Next

isabelle



Le 2016-05-19 17:19, Mike S a écrit :
I'm trying to calculate percentages that a column of values are of a
single cell
value, e.g.
column a contains values 10 through 100
column b will contain the percentage that a is of a cell C6
I'm trying to populate the cell formulas in column B with an =IF(...
formula, is
that possible, I tried this

for n = 1 to 10
Cells(n, 2).Formula = "=IF(C6, Cells(n, 1).Value /
cells(6,3).value & , 0)"
next

But that won't work, I've tried a few other things, I want the sheet
to update
when different values are entered into C6, with the percentages if C6
is not
empty, and with 0's if c6 is empty.

Does anyone have any suggestions?

TIA,
Mike

"Mike S" wrote in message

For n = 34 To 81
Cells(n, 4).Formula = "=IF(D31," & Cells(n, 2).Address & " /" &
Cells(31, 4).Address & ", 0)"
Next

FWIW this would do the same

Cells(34, 4).Resize(81 - 34 + 1).Formula = "=IF($D$31," & Cells(34,
2).Address(0, 0) & "/ $D$31, 0)"

or this

addr0 = Cells(34, 4).Resize(81 - 34 + 1).Address ' could be absolute or
relative
addr1 = Cells(31, 4).Address ' absolute
addr2 = Cells(34, 2).Address(False, False) ' R & C both relative
Range(addr0).Formula = "=IF(" & addr1 & "," & addr2 & "/" & addr1 & ",0)"

or
Range(Cells(34, 4), Cells(81, 4)).Formula = etc

IOW, if a formula can be "filled" it can be written in one go with the
formula of the first cell

Peter T

On 5/20/2016 1:12 AM, Peter T wrote:
"Mike S" wrote in message

For n = 34 To 81
Cells(n, 4).Formula = "=IF(D31," & Cells(n, 2).Address & " /" &
Cells(31, 4).Address & ", 0)"
Next

FWIW this would do the same

Cells(34, 4).Resize(81 - 34 + 1).Formula = "=IF($D$31," & Cells(34,
2).Address(0, 0) & "/ $D$31, 0)"

or this

addr0 = Cells(34, 4).Resize(81 - 34 + 1).Address ' could be absolute or
relative
addr1 = Cells(31, 4).Address ' absolute
addr2 = Cells(34, 2).Address(False, False) ' R & C both relative
Range(addr0).Formula = "=IF(" & addr1 & "," & addr2 & "/" & addr1 & ",0)"

or
Range(Cells(34, 4), Cells(81, 4)).Formula = etc

IOW, if a formula can be "filled" it can be written in one go with the
formula of the first cell

Peter T

Is this the same as copying and pasting the formula? If so I tried that
and maybe I did it incorrectly but it didn't work. I have a single cell
I divide the whole column of numbers by and the reference to that cell
got incremented every time I pasted the formula to a new row. Maybe
there's a different method of pasting that I'm not aware of. I'll read
up on the "resize" command to see what you're doing. I never used or
heard of ".address" before now either, so thank you adn isabelle, I
appreciate the tutoring.

Hi Mike,

Am Thu, 19 May 2016 17:02:37 -0700 schrieb Mike S:

For n = 34 To 81
Cells(n, 4).Formula = "=IF(D31," & Cells(n, 2).Address & " /" &
Cells(31, 4).Address & ", 0)"
Next

you don't need that loop. You can create the formula for the whole range
like Peter wrote.
And you don't have to check for 0. 0 divided by any number is always 0.
Try:
Range("D34:D81").Formula = "=B34/$D$31"


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

"Claus Busch" wrote in message
Hi Mike,

Am Thu, 19 May 2016 17:02:37 -0700 schrieb Mike S:

For n = 34 To 81
Cells(n, 4).Formula = "=IF(D31," & Cells(n, 2).Address & " /" &
Cells(31, 4).Address & ", 0)"
Next

you don't need that loop. You can create the formula for the whole range
like Peter wrote.
And you don't have to check for 0. 0 divided by any number is always 0.
Try:
Range("D34:D81").Formula = "=B34/$D$31"


Indeed, but any number divided by 0 results in an error :)

another way in 2007 or later
Range("D34:D81").Formula = "=IFERROR(B34/$D$31,0)"

Peter T