Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 05-29-2022, 05:10 AM
soroush.kalantari soroush.kalantari is offline Doing Element wise operation by defined name for two or more data set on the same column in excel? Windows 10 Doing Element wise operation by defined name for two or more data set on the same column in excel? Office 2016
Competent Performer
Doing Element wise operation by defined name for two or more data set on the same column in excel?
 
Join Date: Jun 2021
Posts: 115
soroush.kalantari is on a distinguished road
Default 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)
Attached Files
File Type: xlsx example.xlsx (9.1 KB, 10 views)
Reply With Quote
  #2  
Old 05-30-2022, 03:36 AM
ArviLaanemets ArviLaanemets is offline Doing Element wise operation by defined name for two or more data set on the same column in excel? Windows 8 Doing Element wise operation by defined name for two or more data set on the same column in excel? Office 2016
Expert
 
Join Date: May 2017
Posts: 869
ArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud of
Default

There are examples to do what you asked using Defined Tables. Either 3 different Tables, or using a single product Table.
Attached Files
File Type: xlsx TableExample.xlsx (17.4 KB, 7 views)
Reply With Quote
  #3  
Old 06-02-2022, 10:19 AM
p45cal's Avatar
p45cal p45cal is online now Doing Element wise operation by defined name for two or more data set on the same column in excel? Windows 10 Doing Element wise operation by defined name for two or more data set on the same column in excel? Office 2019
Expert
 
Join Date: Apr 2014
Posts: 863
p45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant future
Default

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.)
Reply With Quote
Reply

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
Doing Element wise operation by defined name for two or more data set on the same column in excel? 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

Other Forums: Access Forums

All times are GMT -7. The time now is 04:44 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft