| Site Name | Transaction_End | Sales_Mix_Ind | Payment_Method | Payment_method_Std | Fuel or CR | Fuel | CR | ES | Card |
| Sedgemoor South | 20/05/2018 00:09 | F | Euroshell CRT 2 | ES | |||||
| Sedgemoor South | 20/05/2018 00:09 | C | British Pounds Sterling | cash | |||||
| Sedgemoor South | 20/05/2018 00:09 | C | British Pounds Sterling | cash | |||||
| Sedgemoor South | 20/05/2018 00:55 | F | Mastercard | card | 00:45 | 00:46 | |||
| Sedgemoor South | 20/05/2018 01:00 | F | British Pounds Sterling | cash | |||||
| Sedgemoor South | 20/05/2018 01:46 | F | Euroshell | ES | 00:45 | 00:45 | 01:37 | ||
| Sedgemoor South | 20/05/2018 02:50 | F | British Pounds Sterling | cash | 01:03 | 01:03 | |||
| Sedgemoor South | 20/05/2018 03:30 | F | VISA | card | 00:40 | 00:40 | 02:34 | ||
| Sedgemoor South | 20/05/2018 05:09 | F | Euroshell CRT | ES | 01:39 | 01:39 | 03:23 | ||
| Sedgemoor South | 20/05/2018 05:29 | C | VISA | card | 05:19 | ||||
| Sedgemoor South | 20/05/2018 05:29 | C | VISA | card | 01:59 | ||||
| Sedgemoor South | 20/05/2018 05:38 | F | British Pounds Sterling | cash | |||||
| Sedgemoor South | 20/05/2018 05:50 | C | British Pounds Sterling | cash | |||||
| Sedgemoor South | 20/05/2018 05:50 | C | British Pounds Sterling | cash | |||||
| Sedgemoor South | 20/05/2018 05:50 | C | British Pounds Sterling | cash | |||||
| Sedgemoor South | 20/05/2018 06:16 | C | British Pounds Sterling | cash | |||||
| Sedgemoor South | 20/05/2018 06:18 | C | VISA | card | 00:49 | ||||
| Sedgemoor South | 20/05/2018 06:18 | C | VISA | card | |||||
| Sedgemoor South | 20/05/2018 06:18 | C | VISA | card | |||||
| Sedgemoor South | 20/05/2018 06:32 | F | AllStar | card | 00:54 | ||||
| Sedgemoor South | 20/05/2018 06:33 | C | VISA | card |
<colgroup><col><col><col><col span="2"><col><col span="4"></colgroup><tbody>
</tbody>
Need help in automatically calculating the following columns in Excel and Power BI
The columns are "Fuel or CR", "Fuel", "CR", "ES", Card". In this example, I have manually calculated them, but I need the formula for the same.
This is how they need to be calculated
"Fuel or CR": This is straightforward. Difference between two rows of the Transaction_end column
F5 = B5 - B4
F6 = B6 - B5
and so on and so forth
Note only values greater than 30 mins are mentioned here and less than that have been removed and the field kept blank. Pls note that this step is necessary and I can do the omission myself , if required.
"Fuel",
This depends on the value in the field "Sales_Mix_Ind" depending on whether it is "F"
If its "F", that means Fuel, which means you only substract the corresponding values in the Transaction_end Column
Hence G5 = B5 -B2 as Sales_mix_indicator is "F" in columns C5 and C2. (C3 and C4 are omitted as Sales _Mix_indicator is "C" in those columns)
G7 = B6- B5 as Sales_mix_indicator is "F" in columns C6 and C5
Note only values greater than 30 mins are mentioned here and others are omitted. Pls note that this step is necessary and I can do the omission myself , if required.
"CR",
This depends on the value in the field "Sales_Mix_Ind" depending on whether it is "C"
If its "C", that means Fuel, which means you only substract the corresponding values in the Transaction_end Column
Hence H11 = B11 - B4 as Sales_mix_indicator is "C" in columns C11 and C4
Note only values greater than 30 mins are mentioned here. Pls note that this step is necessary and I can do the omission myself , if required.
"ES",
This depends on the value in the field "Payment_method_std" depending on whether it is "ES"
If its "ES", that means EUroshell, which means you only substract the corresponding values in the Transaction_end Column
Hence I7 = B7 - B2 as "Payment_method_std" is "ES" in columns E7 and E2
And I10 = B10 - B7 as "Payment_method_std" is "ES" in columns E10 and E7
Note only values greater than 30 mins are mentioned here. Pls note that this step is necessary and I can do the omission myself , if required.
Card".
This depends on the value in the field "Payment_method_std" depending on whether it is "card"
If its "card", that means card payment, which means you only substract the corresponding values in the Transaction_end Column
Hence J9 = B9 - B5 as "Payment_method_std" is "card" in columns E9 and E5
And I12 = B12 - B9 as "Payment_method_std" is "card" in columns E12 and E9
Note only values greater than 30 mins are mentioned here. Pls note that this step is necessary and I can do the omission myself , if required.
Pls note that this is just a small subset of data of the total data available to me. Hence this formula needs to be applied on a large excel sheet.
Also note that this data just depicts one site "Sedgemoor South" but in the actual data there will be 5 sites.
Pls help me in getting this formula working
Rgds
Amit
