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
Hello?
Excelchat Expert
16/08/2018 - 03:25
Hello.
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
okay
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
sorry
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
https://docs.google.com/spreadsheets/d/1gPqx5iuwoaA4W4z1-Y8rsi9E5yYGmW68zuEaxa_Z5XE/edit#gid=1735679036
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
https://docs.google.com/spreadsheets/d/1gPqx5iuwoaA4W4z1-Y8rsi9E5yYGmW68zuEaxa_Z5XE/edit#gid=1735679036
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
great
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
Okay.
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
4657.771
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
exactly
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
sure
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
https://docs.google.com/spreadsheets/d/1_3V4WMHdgim8wKaeduUW9uFI1m5zvYRtI1tfWSLi3b8/edit?usp=sharing
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
=SUMIFS($K$16:$K$22,$B$16:$B$22,$B$25)
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.
