Nested IF function or???

[Hael34]

Hello, 

I am trying to create a time sheet that will populate the invoice amount. I have several drop down lists on another tab to choose certain info from that will populate on the first tab. I would like to see how to write a formula to say this:

On TS-1 If 12 - Ft. Myers is chosen from the dropdown list in B-11, it should populate the percentage for that site in cells O-17 & Q-17 respectively so that the sheet will calculate the totals for those rates. Here is the formula I have written already but as you can see, it is not returning the value needed. 

='Dropdown Lists'!E72=IF(AND(OR('Dropdown Lists'!A30,'Dropdown Lists'!A34,'Dropdown Lists'!A50)), N17 + N17*45%)

Another problem is, not all the percentage rates are the same for all the sites. I am not sure if the SWITCH function would work best or the IF function, and even if I did there is so much info it is all floating around in my head, and I can't seem to see a way to get it all tied together.  

Please help if possible. It doesn't seem like I am asking Excel to do something it cannot do; I think I just don't know the correct command. Also I am not able to attach the file, although I feel that would be the best way to see what I am taking about. 

Thanks a million!

[#2]

@Hael34 

You're right that being able to attach a file would be the most helpful, but there are safeguards in place so that brand-new users can't inadvertently upload a virus-infected file.

SO...my sense is that you'd be better served by using a table of the various values that might apply to Ft. Myers, or Sanibel, or Cape Coral (or whatever your various locations are). 

Then use VLOOKUP or one of the other lookup functions.

Let me refer you first to this website where VLOOKUP is explained. https://exceljet.net/excel-functions/excel-vlookup-function

If that isn't sufficient (or doesn't seem to fit) let me invite you to come back and, in the absence of the actual worksheet, describe more fully what is going on, what value(s) are associated with different locations, etc. With that information,  I or or one of the other experts here could create a spreadsheet to demonstrate how it works.......

[#3]

@Hael34 

Here's a very simple example of how VLOOKUP can be used to return a value, based on a selection from a drop down cell. Select the city name, and the value will appear in the yellow background cell, which is where the formula is. This is a very simple example. VLOOKUP can be used with a much more complex table, with more columns. See that reference in my last message for more on how to use it.

[#4]

@mathetes 

Thank you so much for these references. I think the VLOOKUP may work, BUT what I need it to do after that is take that returned value and multiply the % with the value of another cell to return the monetary value I am looking for in the cell with the formula. Let me start from the beginning. Below is a timesheet. Ideally, a pay rate would be entered into the N column.  Based on the chosen value in B-11 where the site dropdown list is the O column would then take the % that VLOOKUP found which matches that site and add the N column value to the N column value * the returned percentage to populate the correct billing rate for that pay rate or =(N18)+(N18*45%). 

The tables where the percentages and sites are located is on the next tab. 

I created a second table for the sites, but I am not sure I needed to and that could be complicating things a bit. 

So, if a payrate is plugged into the N column the worksheet should do the math for all the rates based on the returned percentage for a given site and spit out a total invoice. I hope you have better luck than I have. I cannot seem to get around how to tie all the info together and make it do what I need it to. Maybe there is too much info needing to be calculated??

Thanks so much again for your help on this. I am so stumped, but I know there has to be a way. I love Excel for these kinds of projects. 

[#5]

@Hael34 

Unfortunately the images are too hard to see......

But let me assure you that VLOOKUP can be embedded in another formula, to multiple a given value by a percentage, or increase it by a percentage that is itself looked up in a table.

(I had a career in Human Resources, worked with payroll data all the time; Excel is fully capable of handling your situation. We just need to get a working example that works for you.)

So I've added a level of complexity here to the sample file. Again just an illustration of what's possible.

[#6]

@mathetes 

This doesn't by chance help, does it?

https://docs.google.com/spreadsheets/d/1SaRJk046WFj1EyHN-29lmvS77cBwJOne/edit?usp=sharing&ouid=11438... 

[#7]

@mathetes
Ok, I figured out the VLOOKUP part with this =VLOOKUP(B11,'Dropdown Lists'!A3:D91,2,FALSE). Itt brings back 0.45, which is correct. Now I need it to take that 0.45 (as a percent) and multiply it by the value in another cell. How do I embed a formula into the one I have already in order for the worksheet to do the math? The above link will take you to the worksheet.
Thanks a million!!

[#8]

Yes, the link to Google Sheets work just fine
Now you need to point me to the cell where your VLOOKUP is, and the cell(s) where you're wanting to do those calculations.

[#9]

Great! Ok, the VLOOKUP is on the TS-1 tab, cell O17. The table is on the Dropdown Lists tab, cell A1 - D91.
On the TS-1 tab, I want cell O17 to calculate the regular hourly bill rate, and Q17 to calculate the OT bill rate. O17 should take the appropriate percentage rate (attached on the Dropdowns Lists tab in cells B, C, & D 1-91 and formulated with VLOOKUP) I believe I have Q17 set up properly already. I was able to get part of the way through the formula but ran into a snag when the vlookup did pull the corresponding percentage rate, but the math I did added the percentage as a decimal to the value of N17, which is not correct. Ultimately, the math should be N17 * percentage rate for site listed in B11. Thanks for all your direction.