SOLVED

Sparkline - Not using the rounding numbers

%3CLINGO-SUB%20id%3D%22lingo-sub-2276529%22%20slang%3D%22en-US%22%3ESparkline%20-%20Not%20using%20the%20rounding%20numbers%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2276529%22%20slang%3D%22en-US%22%3E%3CP%3EThe%20sparkline%20in%20my%20cell%20is%20trending%20downward%20although%20the%20value%20is%20the%20same.%26nbsp%3B%20The%20cell%20(U5%2C%20from%20Cookie%20Sales%20tab)%20is%20pointing%20to%20a%20second%20data%20sheet%20(DATA%20for%20U5).%26nbsp%3B%20The%20values%20are%20all%2098.0%25%20(B1%3AD1%2C%20DATA%20for%20U5).%26nbsp%3B%20However%2C%20the%20trend%20line%20is%20trending%20downward.%26nbsp%3B%20I%20took%20the%20same%20values%26nbsp%3B(B1%3AD1%2C%20DATA%20for%20U5)%20and%20I%20expanded%20the%20decimal%20value%20and%20noticed%20that%20when%20it%20is%20expanded%20and%20no%20longer%20rounded%20to%20the%201st%20decimal%2C%20it%20would%20be%20accurate%20for%20it%20to%20trend%20downward.%26nbsp%3B%20However%2C%20I%20need%20for%20the%20trend%20to%20consider%20the%20value%20at%20using%20the%201%20decimal%20place.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20someone%20can%20assist%2C%20I%20would%20greatly%20appreciate%20it!%26nbsp%3B%20Attached%20sample%20file%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20Windows%2010%3C%2FP%3E%3CP%3EMicrosoft%20365%20Apps%20For%20Enterprise%20Version2102%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2276529%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2276698%22%20slang%3D%22en-US%22%3ERe%3A%20Sparkline%20-%20Not%20using%20the%20rounding%20numbers%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2276698%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1028755%22%20target%3D%22_blank%22%3E%40lromero57%3C%2FA%3E%26nbsp%3BChange%20the%20formulae%20that%20calculate%20the%20percentages%20to%3A%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DROUND(A9%2FC9%2C3)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3Eand%20similar%20for%20the%20other%20two.%20You%20formatted%20numbers%20as%20a%20percentage%20with%20one%20decimal%2C%20but%20Excel%20still%20calculates%20with%20the%20unrounded%20numbers.%20The%20above%20formula%20fixes%20the%20outcome%20to%203%20decimals.%20When%20formatted%20as%20a%20percentage%2C%20you%20get%20the%2098.0%25%20for%20all%20three%20and%20the%20sparkling%20will%20be%20horizontal.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Visitor

The sparkline in my cell is trending downward although the value is the same.  The cell (U5, from Cookie Sales tab) is pointing to a second data sheet (DATA for U5).  The values are all 98.0% (B1:D1, DATA for U5).  However, the trend line is trending downward.  I took the same values (B1:D1, DATA for U5) and I expanded the decimal value and noticed that when it is expanded and no longer rounded to the 1st decimal, it would be accurate for it to trend downward.  However, I need for the trend to consider the value at using the 1 decimal place.

 

If someone can assist, I would greatly appreciate it!  Attached sample file

 

I have Windows 10

Microsoft 365 Apps For Enterprise Version2102

1 Reply
best response confirmed by lromero57 (Visitor)
Solution

@lromero57 Change the formulae that calculate the percentages to:

=ROUND(A9/C9,3)

and similar for the other two. You formatted numbers as a percentage with one decimal, but Excel still calculates with the unrounded numbers. The above formula fixes the outcome to 3 decimals. When formatted as a percentage, you get the 98.0% for all three and the sparkling will be horizontal.