Google Spreadsheet Calculate Hours and Amount

Question:

( by Jake )

Here's my spreadsheet:

The equation in Column F should look at Column A to see which individual is listed, then have the equation in Column F multiply the value of Column E by the # which correlates (key in Columns H & I) to each individual in Column A. I'd rather not put a 'key' with the values as in Columns H & I, but if needed I can do that.

So, if Jake is in A, I want hours multiplied by 125 for the final amount in F. If it's Todd, same process, but multiplied by 75, etc.

Also, as a side note, I noticed that the function in Column E doesn't play nicely when hours go from am to pm. Any pretty way of doing this without using military time? If not, it's fine. I'll manage.

Thanks so much for your help!


Solution:

Have a look at the following screenshot of my spreadsheet:

I have the following formula in Cell E1:

=ARRAYFORMULA(IFERROR(IF(row(A:A)=1;"Hours";IF(A:A="";"";HOUR(D:D-C:C)+(MINUTE(D:D-C:C)/60)))))


I have the following formula in Cell F1:

=ARRAYFORMULA(IFERROR(IF(ROW(A:A)=1;"Amount";VLOOKUP(A:A,H4:I7;{2}*SIGN(ROW(A:A));FALSE))))

And now if you put the following formula in Cell F1:

=ARRAYFORMULA(IFERROR(IF(ROW(A:A)=1;"Amount";VLOOKUP(A:A,H4:I7;{2}*SIGN(ROW(A:A));FALSE) * E:E )))

then you will get the following results:

I hope the above solution will help you, and if you need more help then please do comment below on this blog itself, I will try to help you out.

I also take up private and confidential projects:

http://igoogledrive.blogspot.com/p/paid-projects.html

If this blog post was helpful to you, and if you think you want to help me too and make my this blog survive then please donate here: http://igoogledrive.blogspot.com/2012/09/donate.html 

Thanks,

Kishan.

iGoogleDrive