How to rename column title in the Layout || Nexel ?

Hi C Tnu

You could hide the TOTAL column and sum the rows with an algorithm block which you can title anything you like.

Paul Wyatt

Hi Paul !

I tried to use the most simple way to do that but you're right.

I've got some difficulties to use Nexel, I never did it before, I'm trying to understand.

You can see below, to me, the formula should work but it doesn't, my last column just copy the first column of values when I would like the average of this four years (if values <> blank).

Thanks by advance,

I will continue..

Hi C Tnu

The Nexel formulation you need to achieve an average of the rows is AVERAGEIF([range],["criteria"]). Image 1 shows a table listing row items. Each item may have upto three amounts attributed. The last 'average' column calculates the average of the contents of the three amount rows where the amount is over 0.

Image 2 shows the formula used. Points to note about the formula are that the command is AVERAGEIF() which has two arguments, unlike AVERAGE() which only has one. The first argument is the range of columns within the row to be calculated whilst the second argument is the criteria that each range value must meet. The second argument must be enclosed in double quotes " " in order to work. So the formula used in this example is:

=averageif([@d;*;*]:[@f;*;*],">0")

IMPORTANT: You should also note that if you make a change to the block structure, such as adding, deleting or moving a column, you should always re-validate your NEXEL formulas which are static, unlike EXCEL which can dynamically update to compensate for such action.

Image 1: Sampe data and the average of all shown amounts for each row.

Image 2: Nexel editor displaying the formula used to determine the Average of a row IF each range value meets a specified criteria. Note that the criteria argument musts be enclosed in double quotes. So, for totals greater than zero, the criteria is entered as ">0" and not >0. You might also want to use "<>0" to exclude all null values but include negative values.

Hope this helps C Tnu,

Regards,

Paul

Hi C Tnu,

if your salaries are one block and year is by column you can achive the average <> 0 in nexel with =AVERAGEEX( [@a;*;*;Range.Horizontal] ) where a is the block salaries. AVERAGEEX is the average without zero and Range.Horizontal means the whole row of the block a.

Dietmar

Oh Paul I'm really grateful for your kindness and patience.

I go crazy, I tried almost everything and my Nexel still doesn't work. I dont know if it's about the language (work in French)..

My last column stay empty. I tried with averagefi in french, such as Excel, tried to change commas, colons and so on..

I find that Board is not really intuitive.. or maybe am I really crazy ?

Hi,

maybe you can give a bit more Information about your layout. Especially the blocks, entities by column. Have you tried my post: =AVERAGEEX( [@a;*;*;Range.Horizontal] ) where a is the block salaries ?

Dietmar

Hi Dietmar Jeschkeit, thank you for your help !

So you're completely right, my columns entities are salaries per year (2016 to 2019).

I tried with AVERAGEEX but still not working. Maybe I forgot something.

If you're working on Board 11, could I have a screen example of one of ur Nexel formula to compare ?

Thank you by advance,

Have a nice day,

Hi,

please check if AVERAGE is working. Seems that AVERAGEEX is not working. I´ve tried it in 11.1. Then you have to open a ticket for the support Team of BOARD.

yes AVERAGE is working but I would like something like AVERAGEIF because sometimes I've got blank (for example, an employee who worked for a year) and I don't want to count this blank.

The problem with AVERAGE is that it divide by the number of column without any rule. And AVERAGEIF is not working.

For example, in that case, the last column is "AVERAGE". So it count 5030.30/4 = 1257.58 whereas I want 5030.30 in average in that case.

Thank you !!

Hi,

I tried some things.

Here is my solution: =sum([@a;*;*;Range.Horizontal])/[@a;*;*;CountEX.Horizontal]

CountEX is counting the cells <> 0 !

Hope it helps !

Hi C Tnu,

Sorry for providing a BOARD 10 example. Have you selected 'Single Formula'? This is an extra step required for BOARD 11 that was a default setting on BOARD 10.

AVERAGEIF does work in BOARD 11 in the exact same way as I've shown; I don't think the NEXEL engine has changed, just the BOARD interface to access it.

To avoid any confusion, here are the steps to enter the formula you require in BOARD 11.x:

1. Select the dataview to activate it.

-----------------------------------------------------

2. Select FX to access Nexel

-----------------------------------------------------

3. Select [+BLOCK] to add a new block

-----------------------------------------------------

4. Enter a Block Heading and select 'Single formula'

------------------------------------------------------

5. Double click in first cell of new block (column) to enable the formula entry area:

------------------------------------------------------

6. To enter the formula for an 'averageif', type as follows:

a. AVERAGEIF(

b. Select first column of range: [@b;*;*]

c. Enter colon symbol - ':'

d. Select last column of range: [@d;*;*]

e. Enter a single comma ','

f. Enter condition in double quotes: "<>0"

g. Close bracket: ')'

h. Select the green TICK mark to commit formulation.

If you were looking for an average of three row values but only where a value exists, your formula should now look like the line shown below, in the FX area

Let me know how you get on.

Regards,

Paul

Hi Paul Wyatt - Avison Young UK,

i think the layout from C Tnu is different to yours. He hasn´t got 3 (or more) blocks. He has one block and an entity by column. So I think the solution with sum and countex is working for him (listed in the thread - =sum([@a;*;*;Range.Horizontal])/[@a;*;*;CountEX.Horizontal] ). Moreover the solution with sum and countex is more flexibel (if he select more years the Formula will work nevertheless).

Dietmar

Hi C Tnu and many thanks to Dietmar Jeschkeit

Firstly, C Tnu. I apologise for missing the point that you're using a single cube, detailed by the year entity; I should not try to answer posts whilst on holday and using a tablet :-). However, the formula and solution from Dietmar Jeschkeit works so please check the brackets you're using and ensure you've set the formula type. I had the same issues that you described initially. I then entered each part of the formula separately to test them. Once happy, I composited the formulae into one new block.

Dietmar Jeschkeit - Thank you for illustrating how it is possible to sum on a single cube which is detailed by an entity. I had no idea that this was possible and so you have expanded my understanding of NEXEL; I really can't wait to get back to work and begin experimenting with this feature. I've come to realise that I've also never used the NEXEL specific formulations, relying only on my knowledge of EXCEL formulation which I then converted to work in NEXEL. My approach to C Tnu's problem, for example, would have been to use duplicated cubes for the blocks, restricting each with a selection. Clearly there are major drawbacks to this approach like what happens when a new entity member is used and that it becomes unwieldly with more than several members and so is hard to scale.

Kind regards to you both for problem and knowledge sharing.

Paul Wyatt