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

 

[Toadstool]

Hi JenniferMurphy,

I don't see how H5 could be numeric if it is two number on either side of "/". It would need to be text, like the rest of column H.
Something like =$G$5&" / "&$B$7

 

[JenniferMurphy]

Hi JenniferMurphy,

I don't see how H5 could be numeric if it is two number on either side of "/". It would need to be text, like the rest of column H.
Something like =$G$5&" / "&$B$7

If the " / 7" is generated in the cell, as in H7, it would be text. But if it is generated by a custom format, as in I7, then it is a number and the formula in I5 works. The formulas in G7 & I7 are identical. It's just the formatting that's different.

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

Cell Formulas
Range	Formula
C5:G5,I5	C5	=OFFSET(C6,1,0)-OFFSET(C6,2,0)
E7:E13	E7	=C7+D7
F7:F13	F7	=C7/E7
G7:G13,I7:I13	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

Is that clearer?

 

[maabadi]

What is Formula you want use in H5?

 

[JenniferMurphy]

What is Formula you want use in H5?

The same as G5 and I5.

 

[maabadi]

The same as G5 and I5.

Then How result should be different at H5 with G5 or I5, Because data at that cells is same?

 

[JenniferMurphy]

Then How result should be different at H5 with G5 or I5, Because data at that cells is same?

The formulas in G7:G13 and I7:I13 are identical and so is the data. The formulas in G5 & I5 are also identical.

The first part of the formulas in H7:H13 are the same, too, but then they have a second part to add the " / 7". In this version, I put the formula in G5 into H5, but since it is acting on different data in H7, it gets a different result. I thought it would get a #Value error or something, but apparently, it is doing some calculation. I have no idea how it got "90".

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

0 "/" =$B$7

Here's the new version. Row 5 shows the changes in the top row (7) compafred to the following (previous) row (8).

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

Cell Formulas
Range	Formula
C5:I5	C5	=OFFSET(C6,1,0)-OFFSET(C6,2,0)
E7:E13	E7	=C7+D7
F7:F13	F7	=C7/E7
G7:G13,I7:I13	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

I checked several places about custom formatting codes. None of them mention variables or cell values, so I assume it's not possible. I can't think of any other way to do what I want to do.

 

[maabadi]

Why you don't Use Fraction for Number format with this formula at H Column:

=RANK.AVG($F7,OFFSET($F$6,1,0):OFFSET($F$14,-1,0),0) /  OFFSET($B$6,1,0)

 

[JenniferMurphy]

Why you don't Use Fraction for Number format with this formula at H Column:

=RANK.AVG($F7,OFFSET($F$6,1,0):OFFSET($F$14,-1,0),0) /  OFFSET($B$6,1,0)

I do not want a fraction in H7:H13 or I7:I13. I want the ranks (integers). I just want them to look like fractions, but not be fractions.

I don't think we are talking about the same thing. Let's let it go.

Thanks

 

[maabadi]

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))