Excel - COLUMN Function Problem - Expert Solution

Question description:
This user has given permission to use the problem statement for this blog.

i need a formula to look at the criteria in one column and based on this multiply a number in another column by any another in a grid given
Solved by Z. U. in 47 mins
This is the chat thread from the real Excelchat help session. It contains no private user information.
User 16/08/2018 - 03:25
Excelchat Expert 16/08/2018 - 03:25
Excelchat Expert 16/08/2018 - 03:25
I understand that you need to multiply a value depending on the value of another cell, right?
User 16/08/2018 - 03:25
please can you help me with an issue i am having with my G sheet
Excelchat Expert 16/08/2018 - 03:26
I'll help you with this as long as you give me enough information. Before we proceed, this is a reminder that our policy is 1 question per session so for this session we'll be resolving this particular question. We also currently do not support VBA/Macro solutions.
User 16/08/2018 - 03:27
User 16/08/2018 - 03:27
on the right had side is the preview of my workings
Excelchat Expert 16/08/2018 - 03:28
Okay, which cell should I look into?
User 16/08/2018 - 03:28
one column is the currency which in my sheet is data validated with different currencies such as EUR, GBP, USD etc
User 16/08/2018 - 03:29
another column J, is the total sales in the currency specified
Excelchat Expert 16/08/2018 - 03:29
Please refer to such columns with the column names or letters. I'm not familiar with your file so it will take time if you don't directly tell me which columns you are referring to.
User 16/08/2018 - 03:29
User 16/08/2018 - 03:29
column E lists Currencies
User 16/08/2018 - 03:30
column J lists values at the currency on the row of of column E
User 16/08/2018 - 03:32
on another sheet i have a grid A10:G25 where row 10 is currencies and column A is currencies for example
Excelchat Expert 16/08/2018 - 03:33
You will definitely have to show me how this another sheet looks like or we won't get anywhere. Please attach your file so I can analyze it.
User 16/08/2018 - 03:33
User 16/08/2018 - 03:34
this is the G doc
Excelchat Expert 16/08/2018 - 03:34
Please share it to everyone so I can access it.
User 16/08/2018 - 03:34
how do i do that?
Excelchat Expert 16/08/2018 - 03:35
I'm not very familiar with Google sheets but you'll have to change the permission so that everyone can view or edit the file.
Excelchat Expert 16/08/2018 - 03:35
That way, I'll be able to assist you with the formula you need.
Excelchat Expert 16/08/2018 - 03:36
I mean, everyone with the link can view or edit the file.
User 16/08/2018 - 03:36
User 16/08/2018 - 03:36
please try again now
Excelchat Expert 16/08/2018 - 03:37
Okay, I'm in there.
User 16/08/2018 - 03:37
User 16/08/2018 - 03:37
please go to tab "new template"
Excelchat Expert 16/08/2018 - 03:37
Which sheets./
Excelchat Expert 16/08/2018 - 03:37
Excelchat Expert 16/08/2018 - 03:38
So you want to convert the value in column J based on the exchange rates in the grid?
User 16/08/2018 - 03:39
what i am trying to get from C27 is a formula that will recognise the sales person in B25 and the currency in B26 and use that information to work out how much TOTAL sales (in column J) are
User 16/08/2018 - 03:40
information to convert currencies is saved in tab "exchange rates 06.03.2018"
Excelchat Expert 16/08/2018 - 03:41
So in this example, what is the expected output?
Excelchat Expert 16/08/2018 - 03:41
What should the value of C27 be?
User 16/08/2018 - 03:41
so the value should be 2998
Excelchat Expert 16/08/2018 - 03:42
And where does the exchange rate come in?
User 16/08/2018 - 03:42
so ive just made some amendments to the sheet
User 16/08/2018 - 03:43
E17 should now be GBP
Excelchat Expert 16/08/2018 - 03:44
I don't mean to rush you but you'll need to hasten it up a bit if we want the chance to solve this. We have a very limited amount of time to do this and we aren't done with the requirements yet.
User 16/08/2018 - 03:46
the exchange rates come in because if Fleur has the invoice in row 16 as EUR and the invoice in row 17 as GBP i would like to be able to edit B26 to EUR and C27 will work out that J16 is in euros then convert j17 into euros from pound and creat a sum of both
User 16/08/2018 - 03:46
using the exchange rates on the other tab
Excelchat Expert 16/08/2018 - 03:46
So in this case, what should be the expected output for C27?
User 16/08/2018 - 03:48
Excelchat Expert 16/08/2018 - 03:49
So you multiplied 1860 with 0.89235
Excelchat Expert 16/08/2018 - 03:49
Because that's the exchange rate based on your grid.
User 16/08/2018 - 03:50
Excelchat Expert 16/08/2018 - 03:50
I see. Thank you. Let me try to finish this within the time limit.
User 16/08/2018 - 03:50
thank you
User 16/08/2018 - 03:51
then if i were to change B26 to GBP the expected out come would be 4534.705653
Excelchat Expert 16/08/2018 - 03:52
Is it okay to add a new column to help us with the calculations? I think it is possible without one but I'm not sure I can finish it on time without a helper column.
User 16/08/2018 - 03:53
if we need to pay to extent time how much would that be?
Excelchat Expert 16/08/2018 - 03:53
A session can only have a maximum 1 hour, if you want to extend it past that you can but you will have to post the question again and you may not get me next time.
User 16/08/2018 - 03:53
okay please add the column thank you
Excelchat Expert 16/08/2018 - 03:54
Can I add it after column J?
User 16/08/2018 - 03:54
Excelchat Expert 16/08/2018 - 03:58
Nearly done. I don't have access to your file so I can't edit it though.
Excelchat Expert 16/08/2018 - 03:58
I'll have to create a new file and then share you that file.
User 16/08/2018 - 03:59
okay great thank you
Excelchat Expert 16/08/2018 - 04:00
Excelchat Expert 16/08/2018 - 04:00
Please go to this sheet. It'll be exactly like your sheet except I added the helper column due to time constraints.
Excelchat Expert 16/08/2018 - 04:01
Sales Total converted column is what makes this possible.
User 16/08/2018 - 04:01
perfect thank you so much for your help
Excelchat Expert 16/08/2018 - 04:01
You are welcome. Glad to have helped!
Excelchat Expert 16/08/2018 - 04:02
Just a friendly reminder that should you need to contact us again, be sure to have all the information handy so we'll have enough time to solve your question and you won't feel pressured. :)
Excelchat Expert 16/08/2018 - 04:03
Make sure you apply the information I've given you on your file as I'm going to delete my copy to protect your data privacy.
User 16/08/2018 - 04:03
just copying it over now
Excelchat Expert 16/08/2018 - 04:04
Formula for K16:
Excelchat Expert 16/08/2018 - 04:04
=INDEX('EXCHANGE RATES 06.03.2018'!$A$10:$G$25,MATCH('New Template'!$E16,'EXCHANGE RATES 06.03.2018'!$A$10:$A$25,0),MATCH('New Template'!$B$26,'EXCHANGE RATES 06.03.2018'!$A$10:$G$10,0))*J16
Excelchat Expert 16/08/2018 - 04:04
Formula for C27:
Excelchat Expert 16/08/2018 - 04:04
User 16/08/2018 - 04:05
thank you
Excelchat Expert 16/08/2018 - 04:05
You are welcome. Glad to have helped!
Excelchat Expert 16/08/2018 - 04:05
Alright, if you have no other questions regarding the original concern, I'd appreciate a 5-star rating and your feedback if you think I deserve it.
Excelchat Expert 16/08/2018 - 04:05
I'll be giving you high ratings as well so the other experts will know that you are a good client and should be taken care of.
Excelchat Expert 16/08/2018 - 04:05
Please do not forget to click the End Session button otherwise I'll be stuck here until the timer expires and be unable to help others. Thank you.
Excelchat Expert 16/08/2018 - 04:10
FYI, I've deleted the file on my end to protect your data privacy.

This is the output file from the real Excelchat help session:
This is an example of the expert help you can get. It contains no private user information.

Get instant expert help with Excel and Google Sheets

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Your privacy is guaranteed. Your session will not be used for blog unless you give us persmission.

Click here to get your free Excelchat help session

Subscribe to Excelchat.co
Trusted by people who work at
Amazon.com, Inc
Facebook, Inc
Accenture PLC
Siemens AG
The Allstate Corporation
United Parcel Service
Dell Inc