Solved

I need to update an excel formula

Posted on 2014-01-21
4
164 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 250 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 250 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

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Sparklines have been introduced with Excel 2010 and are a useful tool for creating small in-cell charts, used for example in dashboards. Excel 2010 offers three different types of Sparklines: Line, Column and Win/Loss. What it does not offer is a…
Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

707 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now