[Solved] Product If help

Discuss the spreadsheet application

[Solved] Product If help

Postby Umphridas » Mon Apr 29, 2019 11:21 pm

Hello,
I am trying to return a product with multiple ranges of criteria.

When producing a construction estimate, I am trying to have the labor rates auto calculate per task division, based on the specific crew cost. ie: Admin = $65.00/hr ; Lead = $60.00/hr; Laborer = $45.00/hr and so on. This way, whatever crew type I input into the cell, it will multiply the hours estimated by the appropriate rate, and return that number in the proper cell of the labor rate column.

Specific example, if A1:A100 = "crew label" in range D3:D9 then multiply B1* by associated "crew rate" in the range E3:E9.

Does that make sense?
Last edited by Umphridas on Tue Apr 30, 2019 5:48 pm, edited 2 times in total.
OpenOffice 4.1.6 on Windows 10
Umphridas
 
Posts: 3
Joined: Mon Apr 29, 2019 11:07 pm

Re: Product If help

Postby robleyd » Tue Apr 30, 2019 1:37 am

If I am understanding your problem correctly, you might find that VLOOKUP will be a better solution.

If that doesn't help, perhaps you could upload a sample spreadsheet showing examples of what you are trying to achieve. [Forum] How to attach a document here Note maximum file size is 128K.
Cheers
David
Apache OpenOffice Developer Build 4.2.0 9820 - Slackware 14.2 - 64 bit
LibreOffice 6.0.7.3 - Slackware 14.2 - 64 bit
Apache OpenOffice 4.1.4 - Windows 7 Virtual machine
User avatar
robleyd
Moderator
 
Posts: 2858
Joined: Mon Aug 19, 2013 3:47 am
Location: Murbko, Australia

Re: Product If help

Postby MrProgrammer » Tue Apr 30, 2019 2:49 am

Umphridas wrote:Specific example, if A1:A100 = "crew label" in range D3:D9 then multiply B1* by associated "crew rate" in the range E3:E9.
[Tutorial] VLOOKUP questions and answers

If this solved your problem please go to your first post use the Edit button and add [Solved] to the start of the title. You can select the green checkmark icon at the same time.
Mr. Programmer
AOO 4.1.5 Build 9789 on MacOS 10.11.6.   The locale for any menus or Calc formulas in my posts is English (USA).
User avatar
MrProgrammer
Moderator
 
Posts: 3778
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA

Re: Product If help

Postby Umphridas » Tue Apr 30, 2019 5:32 pm

So, VLOOKUP seems like it should work with regard to finding the data, but I'm not seeing how to get the final equation in there. I've uploaded an example sheet to (hopefully) better demonstrate what I'm trying to achieve.

Also, I thank you very much for taking the time to help!!
Attachments
EstimatorHelpSheet.ods
(14.65 KiB) Downloaded 6 times
OpenOffice 4.1.6 on Windows 10
Umphridas
 
Posts: 3
Joined: Mon Apr 29, 2019 11:07 pm

Re: Product If help

Postby FJCC » Tue Apr 30, 2019 5:44 pm

The formula in D6 should be
Code: Select all   Expand viewCollapse view
=B6*VLOOKUP(C6;$H$9:$I$14;2;0)

You should be able to copy that down the column.
Windows 10 and Linux Mint, since 2017
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
FJCC
Moderator
 
Posts: 7220
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: Product If help

Postby Umphridas » Tue Apr 30, 2019 5:47 pm

It goes BEFORE VLOOKUP!!
That seems so easy when you show it.

THANK YOU!!
OpenOffice 4.1.6 on Windows 10
Umphridas
 
Posts: 3
Joined: Mon Apr 29, 2019 11:07 pm


Return to Calc

Who is online

Users browsing this forum: No registered users and 12 guests