Hi,
I have been given the task of converting our companies stores (50 stores in total) weekly budgets to daily. I understand i can just divide the figures by 7 days, however i'm looking formula that i can drag across and down to simplify the process. I have attached a S/S that shows the Store Name, Week Ending & Budget figure in columns A,B&C and the desired daily budgest transposed in rows 1,2,3.
If any has any suggestions, it would be much appreciated.
Thanks in advance.
Something like this?
Pls see the file
G2
=INDEX($C$2:$C$79,MATCH($F2&MIN(IF($B$2:$B$79>=G$1,$B$2:$B$79)),$A$2:$A$79&$B$2:$B$79,0))/7
Confirm COntrl+Shift+Enter
Drag accross and down
Weekly%20to%20Daily%20Budgets(1).xlsx
If you arehappy with the results, please add to the contributor's
reputation by clicking the reputation icon (star icon).
If you are satisfied with the solution(s) provided, please mark your thread as Solved.
Select Thread Tools-> Mark thread as Solved.
To undo, select Thread Tools-> Mark thread as Unsolved.
http://www.excelaris.co.uk
Thank you both for your help. The formula worked perfectly.
Weekly to Daily Budgetsv2.xlsxSorry to reopen this and thanks for everyones help.
The formula worked great, however when i used it on a larger scale and expanded the range in the formula i get a #VALUE. Is there something i'm missing. I have highlight cell G4 in the attached where i have amended the formula to include all the values in colums A,B&C for all stores.
Any help is much appreciated.
G4:
=INDEX($C$2:$C$2704,MATCH($F4&MIN(IF($B$2:$B$2704>=G$1,$B$2:$B$2704)),$A$2:$A$2704&$B$2:$B$2704,0))/7
You have extended the ranges but missed the ">" sign.
Hi Robert,
Thank you for the time you've taken with my post. However, i seem to have missed something. I made your last correction and applied to whole range, but still getting #VALUE. If you wouldn't mind, could you take a look at the updated attached S/S and see where i've gone wrong.
Many thanks
Confirm Formula with COntrol+Shift+Enter
Thanks Robert, that's great and worked perfectly.
You are welcome.
Please add reputation point if you happy with solution.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks