Calculate 1st and Last Average Price using power query formula to return left and right value in calculation

Skip2MiLu

New Member
Joined
Aug 11, 2021
Messages
21
Office Version
  1. 365
Platform
  1. Windows
Hi Guys

I decided to post a new question related to my previous related post that the community helped me on
How to return the right most value in a row using Power Query

Of which the below expression provided did help, however after which I realized that I need to return the left or right most value of specific ranges to used in a follow up formula of which I not entirely sure how to do.
1631003814560.png




So This is My Dataset:
1631002438077.png


Using row 13 in the above picture an example I am looking for 3x Custom Column formulas that will calculate the following answers:

1. 2020 / 2021 1st Price

1.1. Return the 1st or left most (Amt) value in range 01.Jan (Amt) to 12.Dec (Amt) (i.e. R139 230)
1.2. Divide it by
1.3. Return the 1st or left most (Qty) value in range 01.Jan (Qty) to 12.Dec (Qty) (i.e. 600.00)
1.4. Ensure that any possible errors caused by the division returns Null
=R232.05

2. 2020 / 2021 Last Price

1.1. Return the Last or Right most (Amt) value in range 01.Jan (Amt) to 12.Dec (Amt) (i.e. R48 730)
1.2. Divide it by
1.3. Return the Last or Right most (Qty) value in range 01.Jan (Qty) to 12.Dec (Qty) (i.e. 200.00)
1.4. Ensure that any possible errors caused by the division returns Null
=R243.65

3. 2020 / 2021 Average Price

1.1. 2020 / 2021 Total (Amt) (i.e. R555 423)
1.2. Divide it by
1.3. 2020 / 2021 Total (Qty) (i.e. 2 289.00)
1.4. Ensure that any possible errors caused by the division returns Null
=R242.65
This last formula should not be to much of issue



Any help would be much appreciated...
 

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

Forum statistics

Threads
1,223,532
Messages
6,172,875
Members
452,486
Latest member
standw01

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top