Excel Formula for NAV Configuration data

Business Central/NAV Technical Forum
1

Hi there, can you possibly help me? I am busy with a NAV project and have a excel spreadsheet which is used by the customer as well as us (the NAV Partner) to update the configuration data. I want to calculate the percentage of completion based on the following:
Column AY storing the date when the task was completed. I have added a row on top of every task which have a formula to show the following: If the lines below have a date the field is updated as ‘Complete’. If the rows do not have a date in some or all of the lines it shows as ‘Not Complete’

Now I want to update the field in column AZ with a formula to look if the cells have a date in. IF they have I want to see a percentage based on how many lines had been completed. Can anyone help me with the formula?

0336.06-03-2015 10-01-24.png
0336.06-03-2015 10-01-24.png851×471 48.1 KB

2

Maria - well, it’s not a NAV question but I treated your post as a challenge [;)]

Unsolved remains formula to count the steps in each workshop, this is up to you or enter them manually in B5, B9 and so on.

AY5 =IF(COUNTA(OFFSET(AY6;0;0;B5;1))<B5;“Not Complete”;“Complete”)

AZ5 =COUNTA(OFFSET(AY6;0;0;B5;1)) /B5

These can be then copypasted down the sheet, no need to change formulas manually.

2630.Pic1.bmp (1.22 MB)

all columns from D to the right were for ease of testing

6038.Pic2.bmp (1.34 MB)

Enjoy…

3

Hi Modris,

Thank you very much for helping… although it was not a NAV issue :). I will update my spreadsheet today with the formula and really appreciate your help. It will be useful to keep track of the outstanding configuration.

Have a good day!

Maria