# Google Sheet Formula Error

Hoping to get a little help with a formula I am making I have used it in other columns to calculate an outcome, but for some reason when I go to decimals under 0 it give me a #N/A "No Match".

Here is my goal.

• .049 and under will be given a 0
• .05 to .99 will be given a 1
• .1 to .19 will be given a 2
• .2 and above will be given a 3

The formula pulls from three different cells then adds the given number for each up.

Here is the formula I have that I have used in other columns with success.

=sum(IFS(AND(BB3>=0.05,BB3<=0.09),1,AND(BB3>=0.1,BB3<=0.19),2,OR(BB3>=0.2),3,OR(BB3<0.049),0)+IFS(AND(AZ3>=0.05,AZ3<=0.09),1,AND(AZ3>=0.1,AZ3<=0.19),2,OR(AZ3>=0.2),3,OR(AZ3<0.049),0)+IFS(AND(AX3>=0.05,AX3<=0.09),1,AND(AX3>=0.1,AX3<=0.19),2,OR(AX3>=0.2),3,OR(AX3<0.049),0))

I should mention that I am pulling the numbers from a column that is taking the different between two numbers pulled from another sheet within the document. (I know full number inception) Cell BC3 is where I have been working.

Here is a link to the document I have been working on: https://docs.google.com/spreadsheets/d/1itmmnjydelvqw8oYnOQ6IEFCJEqrn79KerRzwpEBN6k/edit?usp=sharing

• Hi and welcome to WebApps. May I just clarify... You have a sheet "Growth" where you collect data for many names (Column A) in at least six groups ("Bench", Squat", etc). Each group has eight columns, one of which is called "BSP" and you have a formula (included in your question) that you use to calculate the BSP value. Your problem in the "Shuttle" group (Cell BC3) where the formula returns #N/A with an error message "No match". Is that broadly correct? Jan 14 at 0:30
• Yes. That is correct Jan 14 at 14:02

In addition to the problem with the score in BC3, the formula involves duplication to the extent that it is prone to error AND makes any change in rates a major undertaking. This answer provides a more simple alternative.

Step#1 - Create a table of scores

Create a table with the respective low scores, high scores, and point values.

Step#2 - Modified formula based on `VLOOKUP`

Use this formula in cell BC3: `=sum(vlookup(BB3,score_analysis!\$A\$2:\$C\$5,3,true)+vlookup(AZ3,score_analysis!\$A\$2:\$C\$5,3,true)+vlookup(AX3,score_analysis!\$A\$2:\$C\$5,3,true))`

Copy the formula down as many rows as their is data.

• I am working through the problem this is doing a better job of giving numbers. However, it is failing to score when the column read .1. I am also wondering what I need to put in for the score_analysis! portion. It is giving me a sheet name unresolved error. Jan 14 at 14:10
• I put my table (for Step#1) in a new sheet. ‘score_analysis’ is the name of that sheet. You can put your table anywhere on your spreadsheet. So far as the problem with “0.1”…. I had the same problem (and I don’t know why it is/was happening). But the ‘vlookup’ approach overcomes it. Jan 14 at 21:16

An observation about the formula in BC3

When the sub-totals of raw values are used, the formula fails.

When the sub-totals are rounded, the formula works.

I don't have a full understanding of how the values in "results sheets" are entered or obtained, but rounding does appear to a be a solution that should solve the problem.

Background

• The formula in BC3 is the `SUM` of three `IFS` formula

• The `IFS` relate to Columns "BB", "AZ" and "AX" respectively.
• At face-value the result in cell BC3 should be 5

• BB = 2
• AZ = 2
• ZX = 1
• Total = 5
• However, the calculations for AZ and AX are failing.

• Both these columns have a value of 0.1 which should generate a result of 2
• The values in the respective "results sheets" appear to be hand-entered and are numbers, so there is no obvious reason why these formula should fail.

Rounding results

• As a test, on Row 4 I created formulas that round the results from row3 in Column AX, AZ, and BB. The rounding was to 3 decimal places.
• `=round(AX3,3)`
• `=round(AZ3,3)`
• `=round(BB3,3)`

When this is done, the formula in Column BB calculates the correct result.

Sample

• This worked perfect. I ended up enter =round(AV3-AW3,3) to calculate the growth column, that then calculated the BSP column as expected. Thank you. Jan 19 at 17:03
• On a tangent, the “growth” values appear to be are calculated by taking the older value minus the more recent value. This seems counter-intuitive to me. I would have said that “growth” would be the more recent value minus the older value. Interesting to note that in the first line of values (which are possibly dummy data), this would result in negative results since the measured performance got worse over time. Jan 19 at 21:34