Forum Discussion
Nested IF function or???
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.
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.
This doesn't by chance help, does it?
https://docs.google.com/spreadsheets/d/1SaRJk046WFj1EyHN-29lmvS77cBwJOne/edit?usp=sharing&ouid=114382848188657363105&rtpof=true&sd=true
- 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!!- 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.