Solved

I need an excel formula

Posted on 2014-01-21
13
287 Views
Last Modified: 2014-01-21
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.
Ex-Ech-1-21-14.xlsx
0
Comment
Question by:wrt1mea
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 3
  • +2
13 Comments
 
LVL 50

Expert Comment

by:barry houdini
ID: 39798073
Try this formula in F2 copied down

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

regards, barry
0
 
LVL 39

Accepted Solution

by:
nutsch earned 250 total points
ID: 39798078
Is this what you are looking for?

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

This adds the result of 1 or 3, rounded up, + 14 to the value in column I.

Thomas
0
 
LVL 23

Assisted Solution

by:NBVC
NBVC earned 250 total points
ID: 39798079
Try:

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

copied down.
0
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 
LVL 50

Expert Comment

by:barry houdini
ID: 39798085
Hey, three for the price of one!

I notice, compared with nutsch and NBVC that I should have used <= rather than < :(

Anyway, here's my formula in a workbook....

regards, barry
add-days.xlsx
0
 
LVL 26

Expert Comment

by:Fred Marshall
ID: 39798112
First, I rewrote the objective description:
1. If the value in column V "Length" is less than or equal to 1500, then the value needs to be divided by 175 and rounded up. In the attached example this would be V2/175=5.7 days (6 days).

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

4. Take the answers from above and add 14.  These represent workdays.
 From the date in column I, compute the date resulting by adding the workdays computed above.

B2 Low feet per day 175   ... that is, cell A2 has "Low feet per day" and cell B2 has value 175
B3 High feet per day 300  ... etc
B4 Length threshold 1500

Formula:
=+WORKDAY(I2,14+ROUNDUP(IF(V2<=$B$4,V2/$B$2,V2/$B$3),0))
0
 
LVL 1

Author Comment

by:wrt1mea
ID: 39798129
WOW! Three Ex Exch Legends for the price of 1! Thanks guys...works great! I appreciate the help.

I will award points, but is there a way to and an iferror in there so that if there arent any values in column V, it returns a blank instead of a value error?
0
 
LVL 50

Expert Comment

by:barry houdini
ID: 39798136
Hello Ted, just try another IF

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

regards, barry
0
 
LVL 23

Expert Comment

by:NBVC
ID: 39798140
With mine and nutsch's formulas, you don't get #N/A... but you can use this to return blank.

=IF(V2="","",WORKDAY(I2,14+ROUNDUP(V2/IF(V2<=1500,175,300),0)))
0
 
LVL 23

Expert Comment

by:NBVC
ID: 39798142
... boy, we're all on the ball today :)
0
 
LVL 1

Author Comment

by:wrt1mea
ID: 39798164
HAHA! Thanks guys, I really appreciate the help! Next time when I have something urgent, I can only hope an pray that you guys are having a day like today!

Thanks again.

Hello Barry...Hope all is well. Thanks for the support!
0
 
LVL 23

Expert Comment

by:NBVC
ID: 39798198
I noticed you missed Barry in your points distribution....
0
 
LVL 39

Expert Comment

by:nutsch
ID: 39798253
Yes, yes, points for Barry!
0
 
LVL 1

Author Comment

by:wrt1mea
ID: 39798287
Well...at the time, I thought he admitted his ommission:

"I notice, compared with nutsch and NBVC that I should have used <= rather than < :( "

In hindsight, I absolutely agree with the others that received points. Barry, you know its not personal, I am sorry! Ha!!!

Do I contact the mods?

Also, I will open another question. And forgive me for my limitations. The formula that you guys helped me answer added days from I2. I need it to subtract from I2. I wasnt getting correct answers simply inserting a "-" sign.
0

Featured Post

[Webinar] Code, Load, and Grow

Managing multiple websites, servers, applications, and security on a daily basis? Join us for a webinar on May 25th to learn how to simplify administration and management of virtual hosts for IT admins, create a secure environment, and deploy code more effectively and frequently.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

738 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