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

rskwalters

New Member
Joined
Feb 2, 2022
Messages
10
Office Version
  1. 365
Platform
  1. Windows
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


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

C3 formula for your new question:

Book3.xlsx
BCD
1
2x33.33%<Format cell as Percentage
3x33.33%<Format cell as Percentage
4
5-
6
7
8
9x
10
11
12
13x
14
15x
16
17x
18x
19x
20
21x
22
23
24
25
26
27
28
Sheet980
Cell Formulas
RangeFormula
C2C2=MIN(COUNTIF(B2:B28,"x")/27,0.63)
C3C3=IF(COUNTIF(B2:B28,"x")/27<0.62,COUNTIF(B2:B28,"x")/27,0.8)
 
Upvote 0

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.
Thank you, I believe that will work for what i'm trying to do. I will leave this thread open today in case I have another question. If I don't, I will close at end of day.
 
Upvote 0
OK, so that formula won't work. There could be lines added or removed which would then change the quantity to divide by. is there another formula that could be used to automatically count the lines? I have tried but can't get it to work. I have this formula but cannot get it to calculate to 63% if over 62.
=COUNTIF(E7:E33,"x")/(COUNTA(E7:E33)+COUNTBLANK(E7:E33)-COUNTIF(E7:E33,"-"))
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,256
Members
448,557
Latest member
richa mishra

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top