SOLVED

spreadsheet formula

%3CLINGO-SUB%20id%3D%22lingo-sub-1938226%22%20slang%3D%22en-US%22%3Espreadsheet%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1938226%22%20slang%3D%22en-US%22%3E%3CP%3Ei%20am%20trying%20to%20create%20a%20formula%20that%20would%20allow%20me%20to%20track%20a%20project%20flow%20chart%20as%20the%20numbers%20i%20input.%26nbsp%3B%3C%2FP%3E%3CP%3Eit%20would%20have%20to%20be%20in%20a%20rows%20first%20lets%20say%20i%20need%20I5%20through%20BP5%20to%20be%20able%20to%20input%20an%20x%20into%2C%20but%20the%20formula%20would%20have%20to%20be%20contingent%20on%20the%20number%20of%20available%20days%20as%20listed%20in%20E5%20the%20formula%20needs%20to%20give%20me%20a%20percentage%20of%20completion%20in%20the%20square%20H5.%26nbsp%3B%3C%2FP%3E%3CP%3Eso%20for%20instance%20the%20project%20is%20listed%20to%20take%205%20days%20and%20i%20have%20three%20x's%20in%20the%20first%20three%20boxes(i%2Cj%2Ck%205)%20in%20square%20H5%20it%20would%20have%20to%20be%20a%20percentage%20of%2060%25%26nbsp%3B%3C%2FP%3E%3CP%3Ecan%20anyone%20help%20me%20out%20here%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1938226%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1938277%22%20slang%3D%22en-US%22%3ERe%3A%20spreadsheet%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1938277%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F884521%22%20target%3D%22_blank%22%3E%40jcsizmadia%3C%2FA%3E%26nbsp%3BThat%20could%20(in%20H5)%20be%20something%20like%3A%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DCOUNTIF(I5%3ABP5%2C%22x%22)%2FE5%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3EFormat%20cell%20H5%20as%20a%20percentage.%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Screenshot%202020-11-29%20at%2006.42.06.png%22%20style%3D%22width%3A%20653px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F236473i09D6CA69E31F698F%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22Screenshot%202020-11-29%20at%2006.42.06.png%22%20alt%3D%22Screenshot%202020-11-29%20at%2006.42.06.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1938634%22%20slang%3D%22en-US%22%3ERe%3A%20spreadsheet%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1938634%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F884521%22%20target%3D%22_blank%22%3E%40jcsizmadia%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAnother%20variant%20for%20the%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F403176%22%20target%3D%22_blank%22%3E%40Riny_van_Eekelen%3C%2FA%3E%26nbsp%3Bmodel%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3D(XMATCH(%2CI5%3ABP5)-1)%2FE5%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3EWith%20that%20it%20doesn't%20matter%20will%20it%20be%20%22x%22%20or%20something%20else%20in%20cells.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

i am trying to create a formula that would allow me to track a project flow chart as the numbers i input. 

it would have to be in a rows first lets say i need I5 through BP5 to be able to input an x into, but the formula would have to be contingent on the number of available days as listed in E5 the formula needs to give me a percentage of completion in the square H5. 

so for instance the project is listed to take 5 days and i have three x's in the first three boxes(i,j,k 5) in square H5 it would have to be a percentage of 60% 

can anyone help me out here

3 Replies
Best Response confirmed by jcsizmadia (New Contributor)
Solution

@jcsizmadia That could (in H5) be something like:

=COUNTIF(I5:BP5,"x")/E5

Format cell H5 as a percentage.

Screenshot 2020-11-29 at 06.42.06.png

@jcsizmadia 

Another variant for the @Riny_van_Eekelen model

 

=(XMATCH(,I5:BP5)-1)/E5

With that it doesn't matter will it be "x" or something else in cells.

 

thanks for the response and the pic it surely helped me out