Excel Formula Help

[its_sam]

I have a spreadsheet with a number of columns each representing a new week.

 

I would like to find the percentage change between the two most recent weeks without having to change the formula each time.

 

I am able to find the percentage change fairly easily, using the method in the link below, but I do not know how to select the two most recent weeks only

 

https://exceljet.net/formula/get-percent-change

[andy_b]

Hard to tell best way to do it without seeing spreadsheet, but attached spreadsheet is one way.

 

demo.xlsx

[Bedders]

Assuming you only enter in a number or date to show each week (something like below), you could use the MAX function and select the range as ($2:$2). Providing you only entered a date that you actually had data for, that should pull the most recent week, and from there you can subtract 7 to get the preceding date (don't forget dates are stored as numbers - 03/07/2017 is 42919, and 26/06/2017 is 42912).

 

[TABLE=class: grid, width: 500, align: left]

[TR]

[TD]week[/TD]

[TD]12/06/2017[/TD]

[TD]19/06/2017[/TD]

[TD]26/06/2017[/TD]

[TD]03/07/2017[/TD]

[/TR]

[TR]

[TD]st(udent) 1[/TD]

[TD]75%[/TD]

[TD]80%[/TD]

[TD]45%[/TD]

[TD]69%[/TD]

[/TR]

[TR]

[TD]st 2[/TD]

[TD]54%[/TD]

[TD]65%[/TD]

[TD]45%[/TD]

[TD]55%[/TD]

[/TR]

[/TABLE]

 

All you then have to do is a HLOOKUP on each date to pull the marks, then subtract the two marks, work out what the total mark is out of 100 to get 1% (assuming it's a test with 60 marks, 60 goes in the total field), then multiply that by the subtraction of both marks. For example:

 

[TABLE=class: grid, width: 400, align: left]

[TR]

[TD][/TD]

[TD=align: center]A[/TD]

[TD=align: center]B[/TD]

[/TR]

[TR]

[TD]1[/TD]

[TD]closest date[/TD]

[TD]=max($2:$2)[/TD]

[/TR]

[TR]

[TD]2[/TD]

[TD]prev date[/TD]

[TD]=CellAbove - 7[/TD]

[/TR]

[TR]

[TD]3[/TD]

[TD]st1 mark 1[/TD]

[TD]hlookup closest date[/TD]

[/TR]

[TR]

[TD]4[/TD]

[TD]st1 mark 2[/TD]

[TD]hlookup prev date[/TD]

[/TR]

[TR]

[TD]5[/TD]

[TD]total (test was out of)[/TD]

[TD]60[/TD]

[/TR]

[TR]

[TD]6[/TD]

[TD]% change[/TD]

[TD]=(B3/B5)-(B4/B5)[/TD]

[/TR]

[/TABLE]

 

Hope this helps

Edited July 6, 2017 by Bedders
removed whitespace

[its_sam]

Thanks andy_b and Bedders, very much appreciated.

 

I now need to work out the number of consecutive drops from week to week.

 

For example, my consecutive fails column at the end needs to display "none" if there have been no changes, "1" if there has been one drop and "2 or more" if there have been two or more consecutive drops.

 

I really hope that is clear.

 

Thanks again!

[Bedders]

Ouch, that looks like either a lot of nested IF's and a COUNTIF, or you could use VBA and make your own function (assuming you have security permissions to do this, and you know that you will only be distributing it amongst people who can enable macros etc.).

 

In which case, you need to get every value from a given row and store it in an array, and then iterate through that array checking each pair of numbers before incrementing your count by 1.

 

Something like the following VBA code in a new Module in Excel Visual Basic:

 

Function CountGradeDrops(CellRange As Range)
 Dim firstNum, secondNum, count, index As Integer
 count = 0
 index = 0
 For Each cell In CellRange
   If index = 0 Then 'needs different logic if this is the first number being stored, as it doesn't need to compare
     firstNum = cell 'cell gets the value within that cell, not the reference e.g. A3
   ElseIf index = 1 Then 'stores second number
     secondNum = cell
   Else
     firstNum = secondNum 'progress second number forward
     secondNum = cell
   End If
   If Not IsNull(secondNum) Then
     If secondNum < firstNum Then count = count + 1 'compare and count if required
   End If
   index = index + 1 'increment index
 Next cell
CountGradeDrops = count
End Function

 

EDIT: Just to say that I know that the above code doesn't work, but I've suddenly got a lot to do (thanks colleagues) and so I posted what I had so far as a pointer.

There's almost certainly a better way to do this than what I've suggested, it's just how I might look at the problem.

Edited July 6, 2017 by Bedders
Added addendum