Repeat calculation on multiple rows

[etheodorou]

Hi hoping someone can help me with my dilemma.  I have created an excel report in NPrinting, which is using level to get a calculated column, which also uses Entity which brings in multiple rows.  One section is for budgets but another section will be filled in manually when the recipients receive the report, however I need to subtract the multiple rows to obtain a variance between the budgets and the manually inputting forecast figure.

Ive attached a basic image of the report to try and show what I need to happen.

Would anyone know if this is possible?  Putting in a basic formula in the Entity cell does not carry over to the other cells.

Thanks

Effie

[Lech_Miszkiewicz · Accepted solution]

Change it from range to table (select area where you have your table-->Insert-->Table) in excel that wil populate records for each line..

regards

Lech

[etheodorou]

Hi thanks for a reply but Ive sent through an image of my actual report template, which as you can see is already set out as a table with levels.  I have entered the formula below but as mentioned it doesnt carry through to all the rows.

I dont think you answer will help with my question but thank you anyway.

Effie

[Lech_Miszkiewicz]

Hi,

In this case if your Variance area is a "Table" entity in Excel you need to make sure that hte calculation is also populated to the row below of your "=IF(Error(D6-D10,"") which effectively will be =IF(Error(D7-D11,"").

I know that htis will create one additional row so you will have to use "deleterow" functionality in NPritntign to remove it.

see screenshots for referenece

cheers

Lech

regular setup:

result:

3. add table and extra expression below

4. add deleterow functionality

[etheodorou]

Hi Lech,

It helped but I think the issue is that in the forecast section, data is manually inputted after the report has been sent to the recipients, so it causes a #ref error.

Is there any work around with this fact?

Effie

[Lech_Miszkiewicz]

Now this becomes an Excel exercise... not NPrinting exercise...

What if you put another IF Statement for variance section... like..

If(D10 = 0,"",IFERROR(D6-D10,""))

did you try this?

regards

Lech

[etheodorou]

Hi Lech,

I have tried that also and still getting the same error.

I do think its still an NPrinting issue in that I am getting six rows in the output due to having more than one entity in the template, and as noted before I am using levels not inserting a straight table.  The formula doesnt take into account that there is multiple rows in the final report.  I've tried various work around's but can get it to repeat the calculation for each row.

Regards

Effie

[Lech_Miszkiewicz]

Well, in this case we would have to start over and plan what source you have, what is required output and re-think whole report build.

Maybe you need to take completly different approach - i am not sure.

regards

Lech

[etheodorou]

Thanks Lech I do appreciate your help in this and yes I might need to rethink how to put this one together.

Regards

Effie