Problem in concatenate

[_Br_10]

Hi everyone!
Today, i'm trying to apply this function:
=if(E4>1;AVERAGE(CONCATENATE(D4;":";"D";TEXTO(INT(LIN(4:4)+E4-1);"#.##0")));D4)
but occurs a problem.
Already trying:
=if(E4>1;AVERAGE(D4&":"&"D"&TEXTO(INT(LIN(4:4)+E4-1);"#.##0")));D4)

but the problem keep up.

What you suggest for me?

[June7]

What is the 'problem' - error message, wrong result, nothing happens? Show sample data and desired result.

[_Br_10]

The error is #Name?
30382

[_Br_10]

I'm working in Portuguese on Excel. So, the function it will be =if(E4>1;AVERAGE(CONCATENATE(D4;":";"D";TEXTO(INT(INDEX(4:4)+E4-1);"#.##0")));D4)

[p45cal]

In words, what are you wanting to show in cell F4?

[_Br_10]

I want to calculate the average of leadtimes, for articles with the same number

[p45cal]

OK, should be possible, can you save us some time by attaching a workbook instead of a picture?

[_Br_10]

30383

[p45cal]

In E2 copied down:
=AVERAGEIF($A$2:$A$488,A2,$C$2:$C$488)
Should be translated in the attached.

[Paul_Hossler]

=if(E4>1;AVERAGE(CONCATENATE(D4;":";"D";TEXTO(INT(LIN(4:4)+E4-1);"#.##0")));D4)

Seems round about to me

You can use the entire column (e.g. $A:$A) and Excel will skip text cells (Row 1) and cells at the end. No need to try and figure just the specific range (e.g. A2:A345)


Other suggestions

1. Use a pivot table (columns G and H)

2. =AVERAGEIF($A:$A,A16,$C:$C) in col E if your version of Excel supports it (orange)

3. =SUMIF($A:$A,A12,$C:$C)/COUNTIF($A:$A,A12) to calculate average the hard way (yellow)

30388

[_Br_10]

Nice! You can help me. Thank you so much :bow: