IFERROR公式包含平均和除法函数。

huangapple go评论112阅读模式
英文:

IFERROR formula contain with average and divide function

问题

=IFERROR(H58-AVERAGE(H20,H24,H16,H12,H35,H47,H43,H39,H8,H31,H54), "")/IFERROR(AVERAGE(H20,H24,H16,H12,H35,H47,H43,H39,H8,H31,H54), "")

=(D53-AVERAGE(D15,D19,D11,D7,D30,D42,D38,D34,D23,D46))/AVERAGE(D15,D19,D11,D7,D30,D42,D38,D34,D23,D46)

英文:

I am trying to turn those error value to blank but seems the formula below doesn't work.'

=IFERROR(H58-AVERAGE(H20,H24,H16,H12,H35,H47,H43,H39,H8,H31,H54),"")/IFERROR(AVERAGE(H20,H24,H16,H12,H35,H47,H43,H39,H8,H31,H54),"")

The below is the original formula that I let the formula go Value minus average then only go for divide average but some cell still did not contain any formula, then I need to add IFERROR formula to hide the error value in the cell. Anyone can advise a better formula solution on this?

=(D53-AVERAGE(D15,D19,D11,D7,D30,D42,D38,D34,D23,D46))/AVERAGE(D15,D19,D11,D7,D30,D42,D38,D34,D23,D46)

IFERROR公式包含平均和除法函数。

答案1

得分: 0

The IFERROR needs to be applied only once. Applying it separately after the divide operation could still result in a number being divided by 0, which would give an error.

=IFERROR((D53-AVERAGE(D15,D19,D11,D7,D30,D42,D38,D34,D23,D46))/AVERAGE(D15,D19,D11,D7,D30,D42,D38,D34,D23,D46), "")

In Ms365 or Excel 2021, you can use LET to do the average calculation only once:

=LET(
Avg, AVERAGE(D15, D19, D11, D7, D30, D42, D38, D34, D23, D46),
IFERROR((D53-Avg)/Avg, "")
)
英文:

The IFERROR needs to be applied only once. Applying it separately after the divide operation could still result in a number being divided by 0, which would give an error.

=IFERROR((D53-AVERAGE(D15,D19,D11,D7,D30,D42,D38,D34,D23,D46))/AVERAGE(D15,D19,D11,D7,D30,D42,D38,D34,D23,D46), "")

In Ms365 or excel 2021, you can use LET to do the average calculation only once:

=LET(
Avg, AVERAGE(D15, D19, D11, D7, D30, D42, D38, D34, D23, D46),
IFERROR((D53-Avg)/Avg, "")
)

huangapple
  • 本文由 发表于 2023年2月16日 11:03:26
  • 转载请务必保留本文链接:https://go.coder-hub.com/75467435.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定