|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Doing Element wise operation by defined name for two or more data set on the same column in excel?
My routine work in excel is such that I should constantly make operation on two or more data set on the same column. (For example, According to sample-attached file, range B2:B7 is Sales Value and range B8:B13 is Sales Volume and in order to get price, I want to have B2:B7 divided byB8:B13). To make such operations easier, I have defined name for each data set (for example I have defined name “Sales. Value for B2:B7 and Sales. Volume for B8:B13) and Now I am seeking an option in Excel which give me the possibility to divide two date sets (Sales. Value and Sales. Volume) by using difined name (something like element wise operation in Matlab (Sales. Value.*/Sales. Volume)). Is there a way to do that? (Although may in the example attached file, doing such endeavor dose not worth the effort, In my real routine work, which is more complex consisting of many such data sets which are not necessarily adjacent to each other, doing such operation make many difference and make the formulas much readable and "error identification" much easier) |
#2
|
|||
|
|||
There are examples to do what you asked using Defined Tables. Either 3 different Tables, or using a single product Table.
|
#3
|
||||
|
||||
Office365 lets you do this; the formula
=sales.value/sales.Volume in cell C14 will spill the results down to cell C19. With earlier versions you should be able to select cells C14 to C19 and type in the same formula, but commit the formula to the whole selected range using Ctrl+Shift+Enter rather than the usual plain Enter. (I haven't checked this.) |
Tags |
defined name |
Thread Tools | |
Display Modes | |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Need to copy specific ranges column data as row data from one worksheet to another using excel VBA | ganesang | Excel Programming | 1 | 03-26-2020 06:18 AM |
Word Error- 'the name in the end tag of the element must match the element type in the start tag' | Eaqox | Word | 6 | 07-25-2019 02:50 AM |
'the name in the end tag of the element must match the element type in the start tag' | NancyL218 | Word | 1 | 05-29-2018 03:18 PM |
Auto-filter horizontally column wise on basis of date | shoaib1989 | Excel | 0 | 02-18-2016 11:16 PM |
How to fetch data from one sheet to another with complete details and date wise as well | harisjawed86 | Excel Programming | 1 | 08-05-2014 09:10 PM |