• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 296
  • Last Modified:

I need an excel formula

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
wrt1mea
Asked:
wrt1mea
  • 4
  • 3
  • 3
  • +2
2 Solutions
 
barry houdiniCommented:
Try this formula in F2 copied down

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

regards, barry
0
 
nutschCommented:
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
 
NBVCCommented:
Try:

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

copied down.
0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
barry houdiniCommented:
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
 
Fred MarshallPrincipalCommented:
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
 
wrt1meaAuthor Commented:
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
 
barry houdiniCommented:
Hello Ted, just try another IF

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

regards, barry
0
 
NBVCCommented:
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
 
NBVCCommented:
... boy, we're all on the ball today :)
0
 
wrt1meaAuthor Commented:
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
 
NBVCCommented:
I noticed you missed Barry in your points distribution....
0
 
nutschCommented:
Yes, yes, points for Barry!
0
 
wrt1meaAuthor Commented:
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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 4
  • 3
  • 3
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now