Excel BI Challenge Workout 002

Workouts Excel BI Challenge
1

— CAN YOU SOLVE THIS - EXCEL CHALLENGE 2 —

Provide a formula to sum last 3 non-zeros values in range A2:A10.
Last 3 non-zeros values are 20, 8 and 16, hence answer should be 44.

(Post answers in Comment)

Download Practice file from https://lnkd.in/dcvT9VrA

#excel, #advancedexcel, #excelchallenge, #excelproblem, #excelquestion, #excelsolution, #excelformulas, #excelfunctions, #exceltips, #exceltricks

image
image855×498 84.6 KB

Excel BI’s LinkedIn Post:

1 Like
2

Quadri Atharu - Last 3 Non Zero Sum Solution .xlsx (1.7 MB)

Formula

=LET(
   d,A2:A10,
   nz,FILTER(d,d<>0),
   SUM(TAKE(nz,-3))
)

d - data
nz - Filtered List of Non-zeros
TAKE(nz,-3) - Last 3 rows of the non-zeros list

2 Likes
3

Just for my own learning, here’s an OfficeScript / TypeScript approach.

function main(wb: ExcelScript.Workbook) {
  const rng = wb.getFirstWorksheet().getRangeByIndexes(1, 0, 9, 1)
  const noNulls: number [] = rng.getValues().map(x => Number(x)).filter(x => x > 0);
  const sumThree: number = noNulls.reverse().slice(0, 3).reduce((total, value) => total + value);
  console.log(sumThree)
}
2 Likes
4

Here’s my Power Query solution

let
Source = Excel.CurrentWorkbook(){[Name=“Table1”]}[Content],
Filter = Table.SelectRows(Source, each ([Data] <> 0)),
KeepLast3 = Table.LastN(Filter, 3),
ToColumns = Table.FromList( List.Transform( Table.ToColumns(KeepLast3), List.Sum), Splitter.SplitByNothing(), {“Answer”}, null)
in
ToColumns

image

1 Like
5

@Melissa ,

Very cool! I know almost nothing about OffiiceScripts - why would someone use this approach rather than Excel forumulas or M code?

Thanks.

  • Brian
1 Like
6

Unlike VBA, OfficeScripts work in the cloud and can be invoked with Power Automate.

I want to explore, practice and learn more about it as I haven’t found much information on how to apply this TypeScript variant to solve common/ practical challenges in Excel.

So to answer your question. No special reason, just for fun…

2 Likes
7 
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Answer = List.Sum(List.LastN(List.RemoveItems(Source[Data], {0}), 3))
in
    Answer
1 Like
8

Here are some of my ways in Excel.

=SUM(TAKE(FILTER(A2:A10, A2:A10), -3))

=SUM(TAKE(TOCOL(IFS(A2:A10, A2:A10), 3), -3))

Last 3 Non Zero Sum.xlsx (17.2 KB)

9

Here are some ways to do it in Power Query.

let
	Source    = Excel.CurrentWorkbook(){[ Name = "data" ]}[Content],
	Filtered  = Table.SelectRows ( Source, each ( [Data] <> 0 ) ),
	LastItems = Table.LastN ( Filtered, 3 ),
	Sum       = List.Sum ( LastItems[Data] )
in
	Sum

let
	Source    = Excel.CurrentWorkbook(){[ Name = "data" ]}[Content][Data],
	Filtered  = List.RemoveItems ( Source, { 0 } ),
	LastItems = List.LastN ( Filtered, 3 ),
	Sum       = List.Sum ( LastItems )
in
	Sum

Last 3 Non Zero Sum.xlsx (23.6 KB)

10

LinkedIn Post by
Tolga Demirci

11

LinkedIn Post by:
Solomon Kinyanjui

Hi apply this formula:
=SUM(OFFSET($A$2,COUNTA($A$2:$A$10)-3,0,COUNT(A2:A10),1))

12

LinkedIn Post by:
Rick Rothstein

=SUM(TRANSPOSE(INDIRECT("A"&LARGE(IF(A2:A10<>0,ROW(A2:A10)),{1,2,3}))))

13

LinkedIn Post by:
Muhammad Nauman

here you go

=SUMPRODUCT(INDEX(A2:A10,LARGE(IF(FREQUENCY(INDEX(MATCH(A2:A10,A2:A10,0)*(A2:A10<>0),,),ROW(A2:A10)-ROW(A1))<>0,ROW(A2:A10)-ROW(A1)),{1,2,3})))

14

LinkedIn Post by:
Rajesh Sinha

E for Excel , you may try this Array CSE formula.

{=SUM(IF(ROW(A2:A10)>=LARGE(IF(A2:A10,ROW(A2:A10) ),3),IF(A2:A10, A2:A10)))}

15

LinkedIn Post by:
Udit Kumar Chatterjee

Here is my solution in #PowerQuery

let
Source = #"Challenge-02",
keepNonZeroRows = Table.SelectRows(Source, each ([Data] <> 0)),
selectLast3Rows = Table.LastN(keepNonZeroRows, 3),
result = List.Sum(Table.Column(selectLast3Rows, "Data"))
in
result
16

LinkedIn Post by:
Victor Momoh (MVP, MOS)

=SUM(TAKE(FILTER(A2:A10,A2:A10<>0),-3))

or with a LET,

=LET(a,A2:A10,SUM(TAKE(FILTER(a,a<>0),-3)))

17

LinkedIn Post by:
Ibrahim Sadiq

=SUM(IFERROR(INDEX(A2:A10,SEQUENCE(Count(A2:A10),,COUNTIF(A2:A10,”<>”&0)-2)),””))

18

LinkedIn Post by:
Daniel Madhadha

=SUM(TAKE(FILTER(A2:A10,A2:A10<>0),-3))

19

LinkedIn Post by:
Meni Porat

=LET(a,FILTER(A2:A10,A2:A10>0),SUM(INDEX(a,COUNT(a)-2,),INDEX(a,COUNT(a)-1,),INDEX(a,COUNT(a),)))

20

LinkedIn Post by:
Bhavya Gupta

=SUM(TAKE(FILTER(Rng, Rng<>0),-3))