How to achieve this in Excel

A

AMITKOL

New Member
Joined
Jan 15, 2019
Messages
1
[TABLE="width: 946"]
<colgroup><col><col><col><col span="2"><col><col span="4"></colgroup><tbody>[TR]
[TD]Site Name[/TD]
[TD]Transaction_End[/TD]
[TD]Sales_Mix_Ind[/TD]
[TD]Payment_Method[/TD]
[TD]Payment_method_Std[/TD]
[TD]Fuel or CR[/TD]
[TD]Fuel[/TD]
[TD]CR[/TD]
[TD]ES[/TD]
[TD]Card[/TD]
[/TR]
[TR]
[TD]Sedgemoor South[/TD]
[TD="align: right"]20/05/2018 00:09[/TD]
[TD]F[/TD]
[TD]Euroshell CRT 2[/TD]
[TD]ES[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Sedgemoor South[/TD]
[TD="align: right"]20/05/2018 00:09[/TD]
[TD]C[/TD]
[TD]British Pounds Sterling[/TD]
[TD]cash[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Sedgemoor South[/TD]
[TD="align: right"]20/05/2018 00:09[/TD]
[TD]C[/TD]
[TD]British Pounds Sterling[/TD]
[TD]cash[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Sedgemoor South[/TD]
[TD="align: right"]20/05/2018 00:55[/TD]
[TD]F[/TD]
[TD]Mastercard[/TD]
[TD]card[/TD]
[TD="align: right"]00:45[/TD]
[TD="align: right"]00:46[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Sedgemoor South[/TD]
[TD="align: right"]20/05/2018 01:00[/TD]
[TD]F[/TD]
[TD]British Pounds Sterling[/TD]
[TD]cash[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Sedgemoor South[/TD]
[TD="align: right"]20/05/2018 01:46[/TD]
[TD]F[/TD]
[TD]Euroshell[/TD]
[TD]ES[/TD]
[TD="align: right"]00:45[/TD]
[TD="align: right"]00:45[/TD]
[TD] [/TD]
[TD="align: right"]01:37[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Sedgemoor South[/TD]
[TD="align: right"]20/05/2018 02:50[/TD]
[TD]F[/TD]
[TD]British Pounds Sterling[/TD]
[TD]cash[/TD]
[TD="align: right"]01:03[/TD]
[TD="align: right"]01:03[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Sedgemoor South[/TD]
[TD="align: right"]20/05/2018 03:30[/TD]
[TD]F[/TD]
[TD]VISA[/TD]
[TD]card[/TD]
[TD="align: right"]00:40[/TD]
[TD="align: right"]00:40[/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]02:34[/TD]
[/TR]
[TR]
[TD]Sedgemoor South[/TD]
[TD="align: right"]20/05/2018 05:09[/TD]
[TD]F[/TD]
[TD]Euroshell CRT[/TD]
[TD]ES[/TD]
[TD="align: right"]01:39[/TD]
[TD="align: right"]01:39[/TD]
[TD] [/TD]
[TD="align: right"]03:23[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Sedgemoor South[/TD]
[TD="align: right"]20/05/2018 05:29[/TD]
[TD]C[/TD]
[TD]VISA[/TD]
[TD]card[/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]05:19[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Sedgemoor South[/TD]
[TD="align: right"]20/05/2018 05:29[/TD]
[TD]C[/TD]
[TD]VISA[/TD]
[TD]card[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]01:59[/TD]
[/TR]
[TR]
[TD]Sedgemoor South[/TD]
[TD="align: right"]20/05/2018 05:38[/TD]
[TD]F[/TD]
[TD]British Pounds Sterling[/TD]
[TD]cash[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Sedgemoor South[/TD]
[TD="align: right"]20/05/2018 05:50[/TD]
[TD]C[/TD]
[TD]British Pounds Sterling[/TD]
[TD]cash[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Sedgemoor South[/TD]
[TD="align: right"]20/05/2018 05:50[/TD]
[TD]C[/TD]
[TD]British Pounds Sterling[/TD]
[TD]cash[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Sedgemoor South[/TD]
[TD="align: right"]20/05/2018 05:50[/TD]
[TD]C[/TD]
[TD]British Pounds Sterling[/TD]
[TD]cash[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Sedgemoor South[/TD]
[TD="align: right"]20/05/2018 06:16[/TD]
[TD]C[/TD]
[TD]British Pounds Sterling[/TD]
[TD]cash[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Sedgemoor South[/TD]
[TD="align: right"]20/05/2018 06:18[/TD]
[TD]C[/TD]
[TD]VISA[/TD]
[TD]card[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]00:49[/TD]
[/TR]
[TR]
[TD]Sedgemoor South[/TD]
[TD="align: right"]20/05/2018 06:18[/TD]
[TD]C[/TD]
[TD]VISA[/TD]
[TD]card[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Sedgemoor South[/TD]
[TD="align: right"]20/05/2018 06:18[/TD]
[TD]C[/TD]
[TD]VISA[/TD]
[TD]card[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Sedgemoor South[/TD]
[TD="align: right"]20/05/2018 06:32[/TD]
[TD]F[/TD]
[TD]AllStar[/TD]
[TD]card[/TD]
[TD] [/TD]
[TD="align: right"]00:54[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Sedgemoor South[/TD]
[TD="align: right"]20/05/2018 06:33[/TD]
[TD]C[/TD]
[TD]VISA[/TD]
[TD]card[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]



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
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).
You must log in or register to reply here.

Similar threads

P
  • Question Question
Offsetting dates / usage in vlookup or index/match function
Replies
1
Views
547
GlennUK
G
J
  • Question Question
Need help with scheduling formulas
Replies
0
Views
604
jordan89m
J
X
  • Question Question
Retrive First and last occurrence between specific time intervals
Replies
2
Views
537
Aladin Akyurek
Aladin Akyurek
X
  • Question Question
Getting Effective Office Hours with Access Card Swipe Data - (VBA Preferred)
Replies
2
Views
553
xs4amit
X
K
  • Solved
Help with Macro, as it is not extracting all the data.
Replies
7
Views
964
Fluff
Fluff

Forum statistics

Threads
1,223,903
Messages
6,175,284
Members
452,630
Latest member
OdubiYouth

Share this page

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