Hello All,
There were several solutions posted that were close to answering my question, but I did not find one that I thought I could alter to fit my specific request.
I am trying to write one If statement that will calculate [column 4] the percentage based on user input data [in columns 3 and 5] of number of hours, and frequency.
The worksheet assumes the following: 8 hours= 1 work day; 40 hours = 1 work week; 40 hours per week x 52 weeks per year / 12 months per year = 173.33 average monthly hours; and 2080 hours = work year.
1 2 3 4 5
A Task # Task Description # Hours Percentage Frequency [daily/wkly/monthly]
B 1. Typing 4 50% Daily
C
D
E
Some tasks I complete on a daily basis.
Some tasks I complete on a weekly basis.
Some tasks I complete on a monthly basis.
A1= Task Number
A2= Task Description
A3= User Input for number of hours (which can be per day, week, or month)
A4= Answer cell [based on value entered in C1 and percentage calculated based on E1 [either Daily, Weekly, Monthly]. This assumes an 8 hour working day; 40 hour week; 2080 hours per year.
A5= Text field of Frequency of task [daily, weekly, monthly]
For example, If I type documents on a daily basis for 4 hours per day, my percentage would equal 50%; 4 hours per week would equal 10%; and 4 hours per month would equal [2.3%]
I thought it might look something like: =IF(OR(E5="Daily",100/12.5*C5,""),(E5="Weekly",100/2.5*C5,""),(E5="Monthly",100/(2080/12)*C5,"") but am sure I am missing something...
Bookmarks