Taking an Excel Function and using it in a for loop in r

Conner McCoy 0
Viewed 31

I need some help creating a for loop in r. This loop will then be integrated into SQL.

I have the following formula in excel that determines the ConstrainedOrderQty and need to re-create it in a for loop in r.

=CEILING(MAX(SUM(OrderQtys [1:3])-SUM(ConstrainedOrderQtys [1:2]),0),Min Order Qty)

    OrderQty   Min. Order Qty    Constrained OrderQty
1     86,500         50,000             100,000
2     25,600         50,000              50,000
3     24,000         50,000                0

The OrderQty is the amount needed to order and the min order qty, is the interval we are allowed to order in. So for column, because we need 86,500 units and can only order in increments of 50,000 units, we have to order 100,000 units. For column two, we need 25,600 units and have 13,500 units (100,000 - 86,500) on hand already so we would need to order 50,000 more. For column 3, the constrained orderqty is 0 because we need 24,000 units but have 37,900 units on hand.

Anyone help is appreciated and let me know if I need to elaborate more.

Thanks, Conner

for-loop r excel
1 Answers
library(tidyverse)
library(plyr)

Given your dataset

# A tibble: 3 × 2
  quantity interval
     <dbl>    <dbl>
1    86500    50000
2    25600    50000
3    24000    50000

df %>% 
  mutate(order = round_any(quantity, interval))

# A tibble: 3 × 3
  quantity interval  order
     <dbl>    <dbl>  <dbl>
1    86500    50000 100000
2    25600    50000  50000
3    24000    50000      0
Tom Hoel0
Related