Formula - percentages less than, greater than, actual value using x in a checklist

[rskwalters]

I need help with a formula. End goal is the 33% to show actual value if less than 63%, but show 63% if over 63%. This is a project sheet that will work as a 30/60/90 percent completion. The form will continue to have "x" added as projects are done and assigned. Some boxes will remain blank and some will have a dash (-) added.
I have this formula currently to get the 33% but cannot get past this for the rest of the formula. PS - this was a project that was given to me by someone else who already had the formula's calculated.

Thanks

 

[jtakw]

Hi,

You didn't give any details about the calculation, this is a guess:

Book3.xlsx
	A	B	C	D
1
2		x	33.33%	<Format cell as Percentage
3		x
4
5
6
7
8
9		x
10
11
12
13		x
14
15		x
16
17		x
18		x
19		x
20
21		x
22
23
24
25
26
27
28
Sheet980

Cell Formulas
Range	Formula
C2	C2	=MIN(COUNTIF(B2:B28,"x")/27,0.63)

 

[rskwalters]

This is the formula that is set now. =countif(b2:b28,"x")/(counta(b2:b28)+countblank(b2:b28)-countif(b2:b28,"-"))

goal is to have C2 show the percentage as is if the percentage is less than 63% or show 63% if more than 63%.

 

[jtakw]

This is the formula that is set now. =countif(b2:b28,"x")/(counta(b2:b28)+countblank(b2:b28)-countif(b2:b28,"-"))

goal is to have C2 show the percentage as is if the percentage is less than 63% or show 63% if more than 63%.

Then my formula in Post #2 should work.

EDIT: Unless the "-" means something you haven't explained.

 

[rskwalters]

The form will continue to have "x" added as projects are done and assigned. Some boxes will remain blank and some will have a dash (-) added.

 

[jtakw]

So, if the dash "-" doesn't mean to Exclude, Have you tried my formula in Post # 2, it works as you have described.

 

[rskwalters]

and also, if you have the same set up on another column but need that column that anything over 62% shows 80%. how would that formula be set up?

 

[rskwalters]

So, if the dash "-" doesn't mean to Exclude, Have you tried my formula in Post # 2, it works as you have described.

yes that formula worked! thanks!! See below for another question regarding the same information, but another column.

 

[jtakw]

yes that formula worked! thanks!! See below for another question regarding the same information, but another column.

Took you this long to try my formula...

and also, if you have the same set up on another column but need that column that anything over 62% shows 80%. how would that formula be set up?

You need to be a little more specific.
1. The cell range, is it still 27 rows?
2. What are the parameters? ( is it anything Under 62%, show actual, 62% and over, show 80% ) ?

 

[rskwalters]

Took you this long to try my formula...



You need to be a little more specific.
1. The cell range, is it still 27 rows?
2. What are the parameters? ( is it anything Under 62%, show actual, 62% and over, show 80% ) ?

Yes, same cell range. anything under 62 show actual; 62 and over show 80