Is there a way to put a variable in a custom format?

[JenniferMurphy]

Is there any way that I can put a variable in a custom format?

In this table, I would like to be able to calculate in H5, highlighted in yellow, the value in G5, next to it. I can't do that because of the way I generated the results in the rest of Column H, resulting in a non-numeric value. But if I could put the "/ 7" in the custom format, where the "7" is a variable, then the value would remain numeric.

Book1
	B	C	D	E	F	G	H
5		-3	=0	-3	-11%	+3	+3???
6	Events	Hits	Misses	Tries	%	Rank %	Rank %
7	7	4	2	6	67%	4	4 / 7
8	6	7	2	9	78%	1	1 / 7
9	5	4	2	6	67%	4	4 / 7
10	4	5	3	8	63%	6	6 / 7
11	3	5	2	7	71%	2	2 / 7
12	2	4	2	6	67%	4	4 / 7
13	1	3	4	7	43%	7	7 / 7
14	0
Sheet1

Cell Formulas
Range	Formula
C5:G5	C5	=OFFSET(C6,1,0)-OFFSET(C6,2,0)
E7:E13	E7	=C7+D7
F7:F13	F7	=C7/E7
G7:G13	G7	=RANK.AVG(F7,OFFSET(F$6,1,0):OFFSET(F$14,-1,0),0)
H7:H13	H7	=RANK.AVG(F7,OFFSET(F$6,1,0):OFFSET(F$14,-1,0),0) & " / " & OFFSET($B$6,1,0)
B7:B13	B7	=OFFSET(B7,1,0)+1

Thanks

 

[JenniferMurphy]

What about This formula for H5:

=(LEFT(OFFSET(H6,1,0),FIND("/",OFFSET(H6,1,0))-1)-LEFT(OFFSET(H6,2,0),FIND("/",OFFSET(H6,2,0))-1))/OFFSET($B$6,1,0)

Or

=(LEFT(OFFSET(H6,1,0),FIND("/",OFFSET(H6,1,0))-1)-LEFT(OFFSET(H6,2,0),FIND("/",OFFSET(H6,2,0))-1))

Wow!!! If they are doing what I think they are, it should work. Thanks

 

[maabadi]

You're Welcome & Thanks for Feedback.

 

[Rick Rothstein]

I am asking if there is a way to put the value of a cell or variable in a custom format expression.

I think we can do what you want using event code. If I am right, you would only need one Rank column (G) which would have the calculated rank value (a number) with a Custom Format given to it by the event code so it would look like the "division" you show.. If you want to pursue this, I would need to know the following...

1) Are the values in Column B manually entered? If not, what is the formula in cell B5?

2) What is the formula in cell G5?

3) Just checking... all your examples show the division by what is in cell B5 but your really want the divisor for each row to come from the value in Column B on that same row, correct?

 

[JenniferMurphy]

I think we can do what you want using event code. If I am right, you would only need one Rank column (G) which would have the calculated rank value (a number) with a Custom Format given to it by the event code so it would look like the "division" you show.. If you want to pursue this, I would need to know the following...

1) Are the values in Column B manually entered? If not, what is the formula in cell B5?

They are generated by adding "1" to the cell immediately below. I think this is shown in the last row of the xl2bb cell formulas in post #7 above.

=OFFSET(B7,1,0)+1

2) What is the formula in cell G5?

This is also in thexl2bb table above. The 3 header cells (C5:G5) all have the same formula:

=OFFSET(C6,1,0)-OFFSET(C6,2,0)

3) Just checking... all your examples show the division by what is in cell B5 but your really want the divisor for each row to come from the value in Column B on that same row, correct?

I think you meant B7? B5 has literal text "Changes→".

I want all the "divisors" to use the number in B7. This is shown in the second part of the formula in H7:H13:

 & " / " & OFFSET($B$6,1,0)

These are display divisors, not actual arithmetic divisors. I could have set the display to "4 of 7" instead of "4 / 7".

 

[Rick Rothstein]

Right-click the tab on the worksheet containing your data, select View Code from the popup menu that appears and copy/paste the following code into the code window that appears...

Private Sub Worksheet_Calculate()
  Dim R As Long
  For R = 7 To Cells(Rows.Count, "G").End(xlUp).Row
    Cells(R, "G").NumberFormat = "0 "" / """ & Range("B7").Value
  Next
End Sub

Now, when ever the worksheet is recalculated or a cell's value is changed on the worksheet, the ranks in Column G will become formatted in the way you want but the cell values will still be numbers.

 

[JenniferMurphy]

Right-click the tab on the worksheet containing your data, select View Code from the popup menu that appears and copy/paste the following code into the code window that appears...

Private Sub Worksheet_Calculate()
  Dim R As Long
  For R = 7 To Cells(Rows.Count, "G").End(xlUp).Row
    Cells(R, "G").NumberFormat = "0 "" / """ & Range("B7").Value
  Next
End Sub

Now, when ever the worksheet is recalculated or a cell's value is changed on the worksheet, the ranks in Column G will become formatted in the way you want but the cell values will still be numbers.

It's a little past my bedtime, so my eyes are a bit droopy. I'll take this up first thing in the morning. This may open the door to a whole range of new possibilities.

Thanks