Formula help

Regular Contributor

 

I have the following formula =XLOOKUP(LEFT($I5,3),LEFT('Rate Schedule'!$B5:$B16,3),'Rate Schedule'!C5:C16,"error") in cell K3 on my Jan Tab that is pulling a rate from the Rate Schedule Tab. The Rate shown in the Rate schedule cell C5 is 0.08773%. However; the Rate showing in K3 is showing as 0.0008773. I do not know why there are 2 extra zeros being added to the Rate in K3. When I try to compare my Rates in K6:K355 for which the rates in K6:K355 are mostly 0.08773 the comparison turns out as False. I need some help with this formula so I can do an apples to apples comparison? I just cannot figure out why those 2 extra zeros are being added.  I formatted cell K3 as a percentage and the number showing in K3 is now showing as 0.08773%. This is what is in the Rate Schedule. Now the numbers showing in cells K6:K355 are showing as 0.08773. When the numbers in K6:K355 are compared to K3 even though they look the same, the comparison is returning a False. When I format the cells in K6:K355 to a percentage with 5 decimal places, the numbers change to 8.77300% and of course still return a false. I am using a simple formula to make the comparison =IF(K6=K3,"True","False"). Just to check and I keep getting a False. I am stuck and do not know what to do with this?  I need to compare each number in K6:K355 to the number in K3 using conditional formatting and fill each cell that matches with Green Fill and Yellow font.  If not match, fill cell with Red and font black.  I just cannot get the apple to apple comparison to work.  HELP PLEASE

1 Reply

@Carl_61 

Hi Carl, if you use the percentage formatting, this is what happens:

Multiplies the cell value by 100 and displays the result with a percent (%) symbol. You can specify the number of decimal places that you want to use.

See also here: Available number formats in Excel

To perform your comparison, you can also use the ROUND() function. So for example:

=IF(ROUND(K6,x)=ROUND(K3,x),True)

For x, enter the number of relevant decimal places for your comparison.