Question description:
This user has given permission to use the problem statement for this blog.
I want a formula which says if x =" y" or "z" or "whatever" multiple a by either "x" or "y" but get a VALUE error- any ideas?
=PRODUCT(IF('Historical data'!I5="Research Associate",Costs!$B$18*'Historical data'!F5),PRODUCT(IF('Historical data'!I5="Senior Research Associate A",Costs!$C$18*'Historical data'!F5),PRODUCT(IF('Historical data'!I5="Senior Research Associate B",Costs!$D$18*'Historical data'!F5),PRODUCT(IF('Historical data'!I5="Freelancers",Costs!$E$18*'Historical data'!F5),PRODUCT(IF('Historical data'!I5="Research Director",Costs!$F$18*'Historical data'!F5),PRODUCT(IF('Historical data'!I5="Company Director",Costs!$G$18*'Historical data'!F5),PRODUCT(IF('Historical data'!I5="Managing Director",Costs!$H$18*'Historical data'!F5),"")))))))
Solved by V. Y. in 20 mins
This is the chat thread from the real Excelchat help session. It contains no private user information.
Excelchat Expert
21/08/2017 - 03:04
Welcome, Thanks for choosing Got It Pro-Excel.
Excelchat Expert
21/08/2017 - 03:05
I understand that you need help with formulas
User
21/08/2017 - 03:06
yes please
User
21/08/2017 - 03:06
i'm struggling with a product if formula
User
21/08/2017 - 03:06
=PRODUCT(IF('Historical data'!I5="Research Associate",Costs!$B$18*'Historical data'!F5),PRODUCT(IF('Historical data'!I5="Senior Research Associate A",Costs!$C$18*'Historical data'!F5),PRODUCT(IF('Historical data'!I5="Senior Research Associate B",Costs!$D$18*'Historical data'!F5),PRODUCT(IF('Historical data'!I5="Freelancers",Costs!$E$18*'Historical data'!F5),PRODUCT(IF('Historical data'!I5="Research Director",Costs!$F$18*'Historical data'!F5),PRODUCT(IF('Historical data'!I5="Company Director",Costs!$G$18*'Historical data'!F5),PRODUCT(IF('Historical data'!I5="Managing Director",Costs!$H$18*'Historical data'!F5),"")))))))
Excelchat Expert
21/08/2017 - 03:07
Can you put some samples in the sheet?
User
21/08/2017 - 03:07
keep returning #VALUE result - any ideas where ive gone wrong?
Excelchat Expert
21/08/2017 - 03:08
Are you currently working on MS Excel or Google Sheets?
User
21/08/2017 - 03:09
excel
User
21/08/2017 - 03:09
cant copy it into google sheets it keeps saying reconnecting
Excelchat Expert
21/08/2017 - 03:09
Hmm. even just simple values
Excelchat Expert
21/08/2017 - 03:09
so we can at least replicate what you have in MS Excel.
User
21/08/2017 - 03:09
waiting for it to stop saying reconnecting
User
21/08/2017 - 03:10
at the moment it wont let me type anything in the cell
User
21/08/2017 - 03:10
I will try and explain by text
Excelchat Expert
21/08/2017 - 03:10
Got it
User
21/08/2017 - 03:11
cell A3 may contain a value Research Analyst or Company Director for example
User
21/08/2017 - 03:11
call B4 would contain the associated day cost for research analyst
User
21/08/2017 - 03:11
cell b5 would contain teh associated day cost for company director
User
21/08/2017 - 03:11
ok so far?
Excelchat Expert
21/08/2017 - 03:12
https://docs.google.com/spreadsheets/d/1etupYGaMZ_WO9HSpGQhLlvCKpgOTy2v__w81Fz-LH7o/edit?usp=sharing
User
21/08/2017 - 03:12
cell d3 contains the number of days used on a project
Excelchat Expert
21/08/2017 - 03:12
Try that link it's the same sheet.
User
21/08/2017 - 03:14
have put some data into it
Excelchat Expert
21/08/2017 - 03:14
Ok
User
21/08/2017 - 03:14
so I want to be able to calculate the actual cost based on the resource type used and their associated day rate
User
21/08/2017 - 03:15
in my real sheet there are five different resource types
User
21/08/2017 - 03:15
feels like it should be easy but i'm doing something daft!
Excelchat Expert
21/08/2017 - 03:16
Is Column E resource type?
User
21/08/2017 - 03:17
yes thats the actual resource used
Excelchat Expert
21/08/2017 - 03:17
Associated day rate is which column?
User
21/08/2017 - 03:18
column B
Excelchat Expert
21/08/2017 - 03:19
Can you see my chat from the sheets on the upper-right part of the screen?
Excelchat Expert
21/08/2017 - 03:20
https://docs.google.com/spreadsheets/d/1etupYGaMZ_WO9HSpGQhLlvCKpgOTy2v__w81Fz-LH7o/edit?usp=sharing
Excelchat Expert
21/08/2017 - 03:20
We are running out of time. Please click this link where we can continue chatting for a few minutes after the timer runs out.
Excelchat Expert
21/08/2017 - 03:20
Reply to the chat bubble located in the upper-right corner of that page.
Excelchat Expert
21/08/2017 - 03:22
Hi, I have not received a reply from you for quite some time. I'm afraid the session will end automatically soon.
Excelchat Expert
21/08/2017 - 03:22
Please click the link.
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.
