Percentages

%3CLINGO-SUB%20id%3D%22lingo-sub-2494564%22%20slang%3D%22en-US%22%3EPercentages%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2494564%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20Windows%2010%20Pro%20and%20Excel%20365.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20know%20the%20answer%20is%20a%20simple%20one%2C%20but%20for%20the%20life%20of%20me%2C%20I%20cannot%20wrap%20my%20mind%20around%20what%20it%20should%20be%3A%3C%2FP%3E%3CP%3E1%26nbsp%3B%3CSPAN%3E-%20I%20have%20one%20column%20with%20a%20list%20of%20property%20names%2C%20which%20are%20cells%20%3CSTRONG%3EI1%3AI36.%3C%2FSTRONG%3E%26nbsp%3B%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3E2%20-%20I%20have%20five%20cells%20in%20a%20row%2C%20which%20are%20property%20names%20(e.g.%2C%20Bayside%20Apartments%2C%20etc.).%20Cells%20%3CSTRONG%3EA17%3AE17%3C%2FSTRONG%3E%2C%20four%20of%20the%20five%20cells%20are%20the%20same%20as%20the%20property%20names%20in%20column%20%3CSTRONG%3EI%3C%2FSTRONG%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E3%20-%20I%20have%20one%20cell%20that%20has%20a%20dollar%20amount%20of%20%2410%2C000%2C%20which%20is%20cell%20%3CSTRONG%3EH17%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E4%20-%20I%20have%20five%20cells%2C%20which%20are%20my%20VLOOKUP%20formulas.%20In%20cells%20%3CSTRONG%3EA19%3AE19%3C%2FSTRONG%3E%2C%20the%20formula%20is%20as%20follows%3A%20%3DIF(VLOOKUP(%3CSTRONG%3EA17%3C%2FSTRONG%3E%2C%3CSTRONG%3EI1%3AI36%3C%2FSTRONG%3E%2C1)%3D%3CSTRONG%3EA17%3C%2FSTRONG%3E%2C1%2C0)%20-%20the%20formula%20is%20the%20same%20for%20the%20next%20four%20cells%20in%20the%20row%20with%20the%20exception%20of%20changing%20A17%20to%20B17%20to%20C17%2C%20etc.%3C%2FP%3E%3CP%3E5%20-%20I%20have%20one%20cell%2C%20which%20has%20a%20formula%20in%20cell%20%3CSTRONG%3EH18%3C%2FSTRONG%3E%3A%20%3DSUM(%3CSTRONG%3EA19%3AE19%3C%2FSTRONG%3E)%20-%20giving%20me%20a%20total%20of%204%20(see%20%232%20%26amp%3B%20%234%20above)%3C%2FP%3E%3CP%3E6%20-%20I%20have%20five%20cells%20in%20a%20row%2C%20which%20are%20my%20result%20cells%3A%20%3CSTRONG%3EA18%3AE18%3C%2FSTRONG%3E%20and%20this%20is%20where%20my%20issue%20lies%3C%2FP%3E%3CP%3E7%20-%20In%20row%20%3CSTRONG%3EA18%3AE18%3C%2FSTRONG%3E%2C%20the%20cells%20are%20set%20to%20a%20percentage%2C%20and%20the%20formula%20for%20each%20cell%20is%20as%20follows%3A%20%3DIF(A19%3D1%2C%24H%2417%2F%24H%2418%2C0)%20-%20changing%20A19%20to%20B19%20to%20C19%2C%20etc.%3C%2FP%3E%3CP%3E8%20-%20The%20returning%20results%20in%20cells%20%3CSTRONG%3EA18%3AE18%3C%2FSTRONG%3E%20gives%20me%20200000%25%20and%20not%2020%25%3F%20I%20don't%20know%20why%20the%20result%20is%20coming%20up%20as%20200000%2C%20and%20not%2020%25%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20help%20to%20get%20to%20the%20result%20I%20need%20is%20greatly%20appreciated.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EGaltin%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2494564%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2494613%22%20slang%3D%22en-US%22%3ERe%3A%20Percentages%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2494613%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1090296%22%20target%3D%22_blank%22%3E%40Galtin123%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIt's%20practically%20impossible%20to%20follow%20without%20the%20sample.%20Did%20I%20understand%20correctly%20that%20final%20formula%2C%20if%20ignore%20IF()%2C%3C%2FP%3E%0A%3CP%3E%3D%20H17%2FH18%20%3D%20(%2410000%20%2F%204)%20%3D%202500%20%3D%20250000%20%25%3C%2FP%3E%0A%3CP%3Eand%20you%20would%20like%20to%20receive%2025%25%20%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2494615%22%20slang%3D%22en-US%22%3ERe%3A%20Percentages%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2494615%22%20slang%3D%22en-US%22%3EHello%2C%20Sergei%3CBR%20%2F%3E%3CBR%20%2F%3EYes%2C%20that%20is%20correct.%20I%20would%20like%20to%20see%20the%20final%20result%20show%2025%25.%20I%20can%20send%20you%20the%20sample%20if%20you%20prefer%3F%3C%2FLINGO-BODY%3E
New Contributor

Hello, 

 

I have Windows 10 Pro and Excel 365.

 

I know the answer is a simple one, but for the life of me, I cannot wrap my mind around what it should be:

- I have one column with a list of property names, which are cells I1:I36. 

2 - I have five cells in a row, which are property names (e.g., Bayside Apartments, etc.). Cells A17:E17, four of the five cells are the same as the property names in column I

3 - I have one cell that has a dollar amount of $10,000, which is cell H17

4 - I have five cells, which are my VLOOKUP formulas. In cells A19:E19, the formula is as follows: =IF(VLOOKUP(A17,I1:I36,1)=A17,1,0) - the formula is the same for the next four cells in the row with the exception of changing A17 to B17 to C17, etc.

5 - I have one cell, which has a formula in cell H18: =SUM(A19:E19) - giving me a total of 4 (see #2 & #4 above)

6 - I have five cells in a row, which are my result cells: A18:E18 and this is where my issue lies

7 - In row A18:E18, the cells are set to a percentage, and the formula for each cell is as follows: =IF(A19=1,$H$17/$H$18,0) - changing A19 to B19 to C19, etc.

8 - The returning results in cells A18:E18 gives me 200000% and not 20%? I don't know why the result is coming up as 200000, and not 20%?

 

Any help to get to the result I need is greatly appreciated.

 

Galtin

7 Replies

@Galtin123 

It's practically impossible to follow without the sample. Did I understand correctly that final formula, if ignore IF(),

= H17/H18 = ($10000 / 4) = 2500 = 250000 %

and you would like to receive 25% ?

Hello, Sergei

Yes, that is correct. I would like to see the final result show 25%. I can send you the sample if you prefer?

@Galtin123 

You mention that H17 contains 10000 and that the formula in H18 returns 4.

So H17/H18 = 10000/4 = 2500

If you would format this as a percentage, you'd get 2500*100% = 250000%

I don't see how you could get either 200000% or 20%

Sorry, each cell shows 250000%

@Hans Vogelaar 

In A18:

 

=A19/$H$18

 

Format A18 as a percentage and fill to the right to E18.

@Galtin123 

Better with sample file

I believe I figured it out.
Since the $ amount is $10,000, I have to take the formula =IF(A19=1,((G23/F23)/10000),0)
Where A19 is 1, and G23 is $10,000, and F23 is 4