Thread: Nested IF formula with variable outputs?

1. Nested IF formula with variable outputs?

Hello all - this is my first post. I'm sorry if I don't know what I'm saying - it's because I'm not 100% sure of what I need to use in a formula to achieve my goal. Let me explain. I am a translator and editor. As part of starting with a clean slate in 2017 I've decided to try to further hone my ability to predict how much time I will require for different types of job.

I have broadly two types of regular work coming in: copyediting and translation. Editing usually takes me about half the time but it depends on the complexity of the task. Within each category, there are different levels of difficulty and therefore, time required to do a job. I realized that I could categorize these (as I already do for billing purposes) as essentially, and for my purposes, "Easy/Medium/Hard".

Having looked at some past numbers (I still need to do an in-depth analysis) and doing some back-of-envelope calculations I realized I could base my daily productivity on the number of words I can process for each category whilst also factoring in all the other stuff I do for my business (not directly related to actually processing words + brain-rest time).

I came up with the following rough codes for Copyediting and Translations:

CE1 = 962 words/hour or 4810 words/day
CE2 = 811 w/h or 4055 w/d
CE3 = 685 w/h or 3425 w/d

Tx1 = 642 w/h or 3210 w/d
Tx2 = 550 w/h or 2750 w/d
Tx3 = 208 w/h or 1040 w/d

I want to be able to plug these numbers into excel, combining them with the word count for each job that comes in to give me an estimate — preferably in days/hours/minutes — of the estimated time I will need to do each task. Currently, I am very bad at appreciating this based on the excel tables in front of me so an actual number would be a great start. It would help me with customer quotes and deadline planning, and to know when I might need to out-source work, which I am also really bad at noticing. I like this idea because it means I can tweak the figures as necessary over time, and perhaps even add in extras as necessary.

A very simplified version of my current work management template looks a bit like this, where the "service" refers to the speed at which the customer requires their work be returned:

 A1 A2 A3 A4 A5 1 Project Ref: Service: Code: Words: Estimated Time Required: 2 Cactus Book 2 months Tx2 32482 dd:hh:mm 3 Active commuting to preschool 7 days CE2 3311 dd:hh:mm 4 Real Estate Descriptions January 24 hours Tx1 781 dd:hh:mm 5 The Dark side of cultural policy 4 days Tx3 2402 dd:hh:mm

I'm looking for the way to fill in the dd:hh:mm section automatically via a formula(s).

As an added bonus, if anyone has any idea how I might be able to graphically display this — is it a Gantt chart? or something similar — I will personally purchase the internet for you and deliver it to your door in person, with flowers, and a card!

I apologize for this long and winding post, but I don't know what I need or what to call it, or how to solve this problem which is becoming ever more important as my business grows. I hope you are willing and able to help or point me in the right direction! I have tried to be detailed to avoid misunderstandings and confusions!

Best wishes to all.
Maria

2. see attached.

3. See the attached file:

4. @p45cal
Sorry- I posted without seeing yours, which is a better solution as it preserves the date formatting for totalling etc.

5. Oh my goodness - thank you both! I'm going to try to replicate this in my version immediately. I hadn't seen these posts until now.

6. If you have cross-posted on other forums, then you must provide a link to those cross-posts here, please. Here's why: http://www.excelguru.ca/content.php?184

7. I have posted this thread elsewhere as I wasn't aware of the answers previously posted here. This is the link: www excelforum (dot) com/ showthread.php?t=1169926. Sorry about the link (I do not have enough logged posts on this site to be able to cross reference a link) I have marked that thread as solved and cross-referenced to this forum as the solutions kindly provided by p45cal and Hercules1946 were very helpful. Once again, thanks so much for your help.

8. Originally Posted by Masquette
I have posted this thread elsewhere as I wasn't aware of the answers previously posted here.
Yes, but the point is if we haven't responded yet, its important to let us know about the new posts so that we don't put work into problems solved elsewhere.
This happens all the time, and its really exasperating.

9. Yes. I imagine it does. I'm truly very sorry. I hadn't thought it through properly and because of lack of email notification from this site (admin problem) I assumed nobody was interested in my daft problems, and that people on one site wouldn't be on the other. I'm usually the first to read the rules, but in this case, I was THAT person who didn't do it properly. That's what panic and rushing will do to a person. Obviously, I won't repeat my error again, and you have all been very patient with me. It's the first time I'm using such a forum really, so I hope I'm still welcome. I managed to implement your and p45cal's ideas and produce some really useful information for my day-to-day work so I'm truly very grateful.

10. OK Then, we'll let you off...

As I mentioned above P45cals solution is the one to go with, as Im sure that you will want to tot up the times for your projects, which you cant do with the text strings in mine.