OFFSET ?

D

ds_robb

Board Regular
Joined
Aug 25, 2002
Messages
73
I wonder if someone could help with a nicer and less manual way to do what I am doing. I currently have a list of 12 items (Rows) Column 1 is a rank (1 - 12), column 2 is a country, column 3 is number of units corresponding to that country and column 4 is number of units shipped. Each day I cut and paste the 12 rows below the preceding 12 rows and put in column 5 the following =SUM(D13-IFERROR(VLOOKUP($C13,$C$1:$E$12,2,FALSE),0))

So basically because the items can move in terms of rank, a country wont always be in the same position in the list day by day. So I am checking in the next day's list (commencing row 13) what it's daily unit number is and then looking up the previous days list (range 1-12) and taking the number away. Issue I have right now, is in the formula I have to change $C$1:$E$12 to $C$13:$E$24 and then the next day $C$25:$E$36 etc. Is there anyway using offset or something similar, by copying and pasting I can check the preceeding 12 rows without manually
 

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.
Sort by date Sort by votes
I think that I follow what you are saying, but a visual example of 2 days (dummy data that is an accurate representation of your actual data) would help to clarify and reduce the amount of guesswork needed.

Please use XL2BB (button on reply toolbar) to post your example, not screen captures or copy and paste.
 
Upvote 0
Thanks for the reply and please see attached
Corona.xlsx
BCDEFGHI
2ABCDEFG
3Day 105-Aug1USA48183281579304818328157930
405-Aug2Brazil285907397256285907397256
505-Aug3India190825439795190825439795
605-Aug4Russia8649481446586494814465
705-Aug5South Africa52987792985298779298
805-Aug6Mexico4499614886944996148869
905-Aug7Peru4398902000743989020007
1005-Aug8Chile36472397923647239792
1105-Aug9Columbia3349791131533497911315
1205-Aug10Iran3174831780231748317802
1305-Aug11UK3072584629530725846295
1405-Aug12Spain3057672849930576728499
15Day 206-Aug1USA48767901599904876790159990
1606-Aug2Brazil291221298493291221298493
1706-Aug3India196453640699196453640699
1806-Aug4Russia8701871457987018714579
1906-Aug5South Africa53818496045381849604
2006-Aug6Mexico4561004969845610049698
2106-Aug7Peru4476242022844762420228
2206-Aug8Chile36667198893666719889
2306-Aug9Columbia3457141162434571411624
2406-Aug10Iran3201171797632011717976
2506-Aug11Spain3098552850030985528500
2606-Aug12UK3097844649830978446498
27Day 307-Aug1USA49374411612484937441161248
2807-Aug2Brazil296244299572296244299572
2907-Aug3India202707441585202707441585
3007-Aug4Russia8753781469887537814698
3107-Aug5South Africa54547699095454769909
3207-Aug6Mexico4626905051746269050517
3307-Aug7Peru4554092042445540920424
3407-Aug8Chile36882599583688259958
3507-Aug9Columbia3577101193935771011939
3607-Aug10Iran3225671813232256718132
3707-Aug11Spain3143622850331436228503
3807-Aug12UK3106674659631066746596
Sheet5
Cell Formulas
RangeFormula
H3:H14H3=SUM(F3-IFERROR(VLOOKUP($C3,$C$1549:$E$1560,2,FALSE),0))
I3:I14I3=SUM(G3-IFERROR(VLOOKUP($C3,$C$1549:$E$1560,3,FALSE),0))
H15:H26H15=SUM(F15-IFERROR(VLOOKUP($C15,$C$1561:$E$1572,2,FALSE),0))
I15:I26I15=SUM(G15-IFERROR(VLOOKUP($C15,$C$1561:$E$1572,3,FALSE),0))
H27:H38H27=SUM(F27-IFERROR(VLOOKUP($C27,$C$1573:$E$1584,2,FALSE),0))
I27:I38I27=SUM(G27-IFERROR(VLOOKUP($C27,$C$1573:$E$1584,3,FALSE),0))
C4,C28,C38,C16,C26C4=C3
D4:D14,D28:D38,D16:D26D4=SUM(D3+1)
C5,C10,C13,C29,C34,C17,C22C5=C6
C6,C11,C30,C35,C18,C23C6=C4
C7,C9,C31,C33,C19,C21C7=C10
C8,C32,C20C8=C5
C12,C36,C24C12=C8
C14C14=C7
C25,C37C25=C19
Cells with Conditional Formatting
CellConditionCell FormatStop If True
D27:D38Cell Value=1textNO
D3:D14Cell Value=1textNO
D15:D26Cell Value=1textNO


I have included three days of data, which as you can see if 12 rows repeatedly daily. As countries can vary in rank, there is a lookup to see which value to calculate against previous day. Hope this is correct.
 
Upvote 0
ds_robb said:
to calculate against previous day
Click to expand...
Quick check on one detail, let's assume that a country is ranked as 12th on day 1, 13th on day 2 and 12th on day 3. How should that be handled?
Should it start again with no deduction? In theory, looking back to the previous day with a result, you are counting a 2 day change instead of 1.
 
Upvote 0
Not sure if I'm misinterpreting the question and / or the data, but this is how I see it
Book2
ABCDEFG
205-Aug1USA48183281579304818328157930
305-Aug2Brazil285907397256285907397256
405-Aug3India190825439795190825439795
505-Aug4Russia8649481446586494814465
605-Aug5South Africa52987792985298779298
705-Aug6Mexico4499614886944996148869
805-Aug7Peru4398902000743989020007
905-Aug8Chile36472397923647239792
1005-Aug9Columbia3349791131533497911315
1105-Aug10Iran3174831780231748317802
1205-Aug11UK3072584629530725846295
1305-Aug12Spain3057672849930576728499
1406-Aug1USA4876790159990584622060
1506-Aug2Brazil291221298493531391237
1606-Aug3India19645364069956282904
1706-Aug4Russia870187145795239114
1806-Aug5South Africa53818496048307306
1906-Aug6Mexico456100496986139829
2006-Aug7Peru447624202287734221
2106-Aug8Chile3666719889194897
2206-Aug9Columbia3457141162410735309
2306-Aug10Iran320117179762634174
2406-Aug11Spain3098552850040881
2506-Aug12UK309784464982526203
2607-Aug1USA4937441161248606511258
2707-Aug2Brazil296244299572502301079
2807-Aug3India20270744158562538886
2907-Aug4Russia875378146985191119
3007-Aug5South Africa54547699097292305
3107-Aug6Mexico462690505176590819
3207-Aug7Peru455409204247785196
3307-Aug8Chile3688259958215469
3407-Aug9Columbia3577101193911996315
3507-Aug10Iran322567181322450156
3607-Aug11Spain3143622850345073
3707-Aug12UK3106674659688398
Sheet5
Cell Formulas
RangeFormula
F2:G37F2=D2-SUMIFS(D$1:D1,$C$1:$C1,$C2,$A$1:$A1,$A2-1)
 
Upvote 0
jasonb75 said:
Quick check on one detail, let's assume that a country is ranked as 12th on day 1, 13th on day 2 and 12th on day 3. How should that be handled?
Should it start again with no deduction? In theory, looking back to the previous day with a result, you are counting a 2 day change instead of 1.
Click to expand...
Hi Jason, yes I was only looking at the top 12, so if a country appears in the top 12 on day one, but then goes to 13 on second day and then comes back to 12 on day 3, there is no deduction on Day 2. It only looks for corresponding countries in the list day to day.
 
Upvote 0
jasonb75 said:
Not sure if I'm misinterpreting the question and / or the data, but this is how I see it
Book2
ABCDEFG
205-Aug1USA48183281579304818328157930
305-Aug2Brazil285907397256285907397256
405-Aug3India190825439795190825439795
505-Aug4Russia8649481446586494814465
605-Aug5South Africa52987792985298779298
705-Aug6Mexico4499614886944996148869
805-Aug7Peru4398902000743989020007
905-Aug8Chile36472397923647239792
1005-Aug9Columbia3349791131533497911315
1105-Aug10Iran3174831780231748317802
1205-Aug11UK3072584629530725846295
1305-Aug12Spain3057672849930576728499
1406-Aug1USA4876790159990584622060
1506-Aug2Brazil291221298493531391237
1606-Aug3India19645364069956282904
1706-Aug4Russia870187145795239114
1806-Aug5South Africa53818496048307306
1906-Aug6Mexico456100496986139829
2006-Aug7Peru447624202287734221
2106-Aug8Chile3666719889194897
2206-Aug9Columbia3457141162410735309
2306-Aug10Iran320117179762634174
2406-Aug11Spain3098552850040881
2506-Aug12UK309784464982526203
2607-Aug1USA4937441161248606511258
2707-Aug2Brazil296244299572502301079
2807-Aug3India20270744158562538886
2907-Aug4Russia875378146985191119
3007-Aug5South Africa54547699097292305
3107-Aug6Mexico462690505176590819
3207-Aug7Peru455409204247785196
3307-Aug8Chile3688259958215469
3407-Aug9Columbia3577101193911996315
3507-Aug10Iran322567181322450156
3607-Aug11Spain3143622850345073
3707-Aug12UK3106674659688398
Sheet5
Cell Formulas
RangeFormula
F2:G37F2=D2-SUMIFS(D$1:D1,$C$1:$C1,$C2,$A$1:$A1,$A2-1)
Click to expand...
What I currently do (in my example) is copy the formula from H3 and I3 and paste into H15 and I15, I then have to change the range in the lookup to look back at the previous 12. I then copy that formula down to Row 26. Next day I would copy H15 and I15 and paste into H27 and I27 adjusting the range to look at the previous 12 from there and again copy that formula down to Row 38. What I am looking for is instead of having an absolute range (say $F$15:$G$26) whether I could use OFFSET or something similar. Remembering I have to copy it down 12 rows, so it always needs to point to the absolute range of F15:G26.

Hope that makes sense.
 
Upvote 0
Have you tried the method that I suggested?

If you use the formulas correctly you don't need to adjust the range, the formula that I suggested looks at the date rather than the range.

The formulas in your example are looking ~1500 rows down rather than 12 rows up so it doesn't give me an accurate representation to work with.
With XL2BB (unless you need to edit out personal / confidential data) it is better to copy from the original location of the data rather than copy it to a new location and add extra headers, etc. As you can see, row and column references are added to it automatically.
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Question
How do I 'nudge' data over depending on a transit time
Replies
9
Views
117
mickyd67
M
B
  • Question
Matching Multiple Header Row Cell Values - Copy the data below to next available row.
Replies
1
Views
207
bobaol
B
A
  • Solved
Error with working days in a month
Replies
5
Views
248
Anthony47
A
C
  • Question
Macro for RearrangeColumnsWithRows and sort them
Replies
0
Views
102
CristianFirpo
C
M
  • Question
Help Extracting first and second date from cell and date validation
Replies
0
Views
247
maximillianrg
M

Forum statistics

Threads
1,214,915
Messages
6,122,214
Members
449,074
Latest member
cancansova

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