Concatenate in a string

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

How would i go about combining this string?

=IF(B43=Medicaid_Medical!$A$2,COUNTA(Medicaid_Medical!$A$4:$A$50)/COUNTA(Medicaid_Medical!$A$4:$A$50),0)

I need all of the A's to change according to the column they are
representing. I have A -> Z in a column that I would like to reference so
that when I drag down the letters change accordingly. I know the row #'s will
change without the $ signs when I drag down, but I need the column letters to
change as I go down...

When I try:

=IF(B43=Medicaid_Medical!"&I43&"$2,COUNTA(Medicaid_Medical!"&I43&"$4:"&I43&"$50)/COUNTA(Medicaid_Medical!"&I43&"$4:"&I43&"$50),0)

Replacing the A with the cell that has A in it, it does not work... Is there
another way to do this? Transpose, somehow?
 
Check out the indirect and offset functions in help
(I assume you meant to use count if not counta in the denominater)
if not please change as needed.

=IF(B43=indirect("Medicaid_Medical!"&I43&"$2",COUNTA(offset(indirect("Medicaid_Medical!"&I43&"$4"),0,0,1,47)/COUNTIF(Medicaid_Medical!"&I43&"$4:"&I43&"$50),offset(indirect("Medicaid_Medical!"&I43&"$4"),0,0,1,47)
 
I am trying to get a percent of the number of fields being referenced from
another worksheet, so counta is actually what I am trying to use. (
counta(columnC)/counta(totalplans) to get a percent of fields complete) I am
confused by how the ,0,0,1,47 came into the function? I really appreciate
your help with this
 
The ,0,0,1,47 makes the offset function use the range based on the referenced
cell and 46 cells to the right

the denominator factor in your equation
COUNTA(Medicaid_Medical!$A$4:$A$50)
will be the same as the numerator
COUNTA(Medicaid_Medical!$A$4:$A$50)

I missread your original request and screwed up my response also.

I now assume that the sheet in the numerator or the denominator is
incorrectly referenced.

=IF(B43=indirect("Medicaid_Medical!"&I43&"$2"),COUNTA(offset(indirect("Sheet1!"&I43&"$4"),0,0,1,47))/COUNTA(offset(indirect("Sheet2!"&I43&"$4"),0,0,1,47)),0)
 
After a little bit of playing around, I finally got it working....! Here is
what I did:

=IF(B44=INDIRECT("Medicaid_Medical!"&I44&"$2"),COUNTA(OFFSET(INDIRECT("Medicaid_Medical!"&I44&"$2"),2,0,47,1))/COUNTA(States_Medical),0)

The 47 and 1 were reversed, and the "States_Medical" will always be the
denominator for the reference.

Thanks man, that was of much help!
 
Back
Top