I am trying to use COUNTIF in nexel but it is not giving me what I am expecting. The formula is as follows for each block in the Priority column: -

=COUNTIF ( [@f;*;Range(1:7)], "<=" & [@f;1;*] )

=COUNTIF ( [@f;*;Range(1:7)], "<=" & [@f;2;*] )

=COUNTIF ( [@f;*;Range(1:7)], "<=" & [@f;3;*] )

=COUNTIF ( [@f;*;Range(1:7)], "<=" & [@f;4;*] )

=COUNTIF ( [@f;*;Range(1:7)], "<=" & [@f;5;*] )

=COUNTIF ( [@f;*;Range(1:7)], "<=" & [@f;6;*] )

=COUNTIF ( [@f;*;Range(1:7)], "<=" & [@f;7;*] )

This is what it is giving me in Board.

Product | Min Qty | Safety Stock | Max Qty | Daily Sales Qty | FG Stock | Stock Days | Priority |
---|---|---|---|---|---|---|---|

1 | 7048 | 42285 | 211427 | 7472 | 84571 | 11.318 | 6 |

2 | 30504 | 85411 | 228780 | 32341 | 128117 | 3.961 | 1 |

3 | 13677 | 76594 | 205161 | 14501 | 114890 | 7.923 | 4 |

4 | 2668 | 30011 | 80030 | 2828 | 60022 | 21.224 | 6 |

5 | 4506 | 18296 | 337956 | 4777 | 28388 | 5.943 | 4 |

6 | 9683 | 27113 | 242084 | 10266 | 40670 | 3.962 | 3 |

7 | 31528 | 39410 | 204933 | 33427 | 78820 | 2.358 | 0 |

This is what the same calculation gives me in Excel.

A | B |
---|---|

11.318 | 6 |

3.961 | 2 |

7.923 | 5 |

21.224 | 7 |

5.943 | 4 |

3.962 | 3 |

2.358 | 1 |

=COUNTIF(A$2:A$8,"<="&$A2)

=COUNTIF(A$2:A$8,"<="&$A3)

=COUNTIF(A$2:A$8,"<="&$A4)

=COUNTIF(A$2:A$8,"<="&$A5)

=COUNTIF(A$2:A$8,"<="&$A6)

=COUNTIF(A$2:A$8,"<="&$A7)

=COUNTIF(A$2:A$8,"<="&$A8)

Thanks in advance

Hi Andrew Smeets,

Might be due to rounding in dataviews cells - can you round the numbers to 3 decimal digits and check if it's still the case?

Michele