Improve company productivity with a Business Account.Sign Up

x
?
Solved

I need to update an excel formula

Posted on 2014-01-21
4
Medium Priority
?
225 Views
Last Modified: 2014-01-22
Just like last question, except instead of adding to the date in coumn V, I need it to subtract the total days from it i.e., 14 + X number of days

Here is the original post:

I need an excel formula to look at the value of certain cells.

1. If the Length of the value in column V is less than or equal to 1500, then the length needs to be divided by 175. In the attached example this would be V2/175=5.7 days (6 days).

2. Take the answer from number 1 (6 days)and add 14 more days to the value in column I. These only need to be Network Days, not counting Saturday or Sundays

3. If the Length of the value in column V is greater than 1500, then the length needs to be divided by 300. In the attached example this would be V3/300=5 days (5 days).

4. Take the answer from number 3 (4 days) and add 14 more days to the value in column I. These only need to be Network Days, not counting Saturday or Sundays

5. If day ends up being a fraction, 4.32 days, round up to 5 days.
0
Comment
Question by:wrt1mea
  • 2
4 Comments
 
LVL 39

Assisted Solution

by:nutsch
nutsch earned 1000 total points
ID: 39798351
Try this update:

=WORKDAY(I2,-14-ROUNDUP(V2/IF(V2<=1500,175,300),0))

Thomas
0
 
LVL 50

Accepted Solution

by:
barry houdini earned 1000 total points
ID: 39798445
Hey Ted!

Thomas' version works great, I think, but just for some variety I'll stick with CEILING, i.e.

=WORKDAY(I2,-CEILING(14+V2/IF(V2>1500,300,175),1))

regards, barry
0
 
LVL 1

Author Comment

by:wrt1mea
ID: 39798485
OK guys....I will test in the morning...Thanks!
0
 
LVL 1

Author Closing Comment

by:wrt1mea
ID: 39800403
WORKS GREAT!!!!!!!!!
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Microsoft's Excel has many features that most people will never need nor take advantage of.  Conditional formatting is one feature that you may find a necessity once you start using it.
This article describes a serious pitfall that can happen when deleting shapes using VBA.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…
Enter Foreign and Special Characters Enter characters you can't find on a keyboard using its ASCII code ... and learn how to make a handy reference for yourself using Excel ~ Use these codes in any Windows application! ... whether it is a Micr…

589 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question