Excel Formula

  • Status: Closed
  • Prize: $10
  • Entries Received: 12
  • Winner: num13

Contest Brief

You will write just one formula in excel. File is attached. The first correct answer gets the prize.
*NOTE: Formula should use Stock, Sales, Rate, Priority and Limits. No manual number entry!

- We have one product. And 7 different color versions of it.
- We can paint our products into another colors to adjust our stock rates.
- Every color has different stocks and sell-out figures.
- Rate is: Stock/Monthly Selling

Our target is:
TARGET: If a color has more rate than X (upper limit), paint "some of it" into another color which has less rate then Y (lower limit), according to the priorities.

Priority means: I take from high rate colors and paint low rate colors starting from the lowest priority. When its rate reached Y, I skip to the next.

Write the formula which finds how much we should paint from each color.

Formula should be able to:

1) If there is not enough quantity, you cannot go less then X (even though some colors rates' couldnt reach Y)
2) If rate is between Upper and lower, do not touch that color
3) If rate is N/A do not touch that color.
4) Formula should NOT use any numbers. All parametric. (It should use all the information given)

Recommended Skills

Employer Feedback

“Thank you for great job!”

Profile image someka, Turkey.

Public Clarification Board

  • someka
    Contest Holder
    • 8 years ago

    Thanks for everyone who spent effort to read this long(!) project description and submitted entries.I was not expecting this much attendance. Great community!

    • 8 years ago
  • Kashihami
    Kashihami
    • 8 years ago

    Hi I have submitted my two designs, first one minimizing the rate by increasing the colors with more sales/month and second design keeping in view all your constraints and including the priority as well. I had also send u the single formula as well.. take care

    • 8 years ago
  • vgavrilovag
    vgavrilovag
    • 8 years ago

    Hello, regarding the color priority, it starts from the lowest rate up or is an internal clasification?

    • 8 years ago
    1. someka
      Contest Holder
      • 8 years ago

      1: most prior / 7: least prior

      • 8 years ago
    2. aljar
      aljar
      • 8 years ago

      So basically you start painting into the color with priority of 1?

      • 8 years ago
  • aljar
    aljar
    • 8 years ago

    Hello. I uploaded my entry to the project. Just one question. In the case that there is an item A with rate of 15 and an item B of rate 7. If They are the only items out of the range of rate, then we need to paint A into B until the rate of B reaches Y. But if the rate of item A is still above X, what will the formula do? Will it stop there and let rate of A above X and rate of B just Y, or will it continue the conversion of A to B?

    • 8 years ago
  • rodmendoza
    rodmendoza
    • 8 years ago

    Does it need to be a formula or it could use macros?

    • 8 years ago
  • HusseinRubaie
    HusseinRubaie
    • 8 years ago

    you are telling us when we paint we use +1 and -1, and at the same time you are saying no manual numbers!! How will we know the number of products we will paint?

    • 8 years ago
  • Burpomatic
    Burpomatic
    • 8 years ago

    I could solve your problem, but not with Excel formulas alone.

    • 8 years ago
  • StefanStam
    StefanStam
    • 8 years ago

    I need explanation: when I take from some colors does it depend on it's priority (colomn Color priority) or it's rate (colomn Rate) ?

    I'm trying to solve your task. Thank you.

    • 8 years ago
    1. someka
      Contest Holder
      • 8 years ago

      If we are deciding to paint two products into another colors we make this directly proportional with their rates.

      • 8 years ago
  • dimitraevi
    dimitraevi
    • 8 years ago

    Hello. Can you please explain how you mean to "paint" some of a color into another color?

    • 8 years ago
    1. someka
      Contest Holder
      • 8 years ago

      Just paint! When we paint a black product into green it means black stock -1, green stock +1

      • 8 years ago
  • mnazmih1985
    mnazmih1985
    • 8 years ago

    if you can contact me to send you the excel sheet directly it will be better that way

    • 8 years ago

Show more comments

How to get started with contests

  • Post your contest

    Post Your Contest Quick and easy

  • Get tons of entries

    Get Tons of Entries From around the world

  • Award the best entry

    Award the best entry Download the files - Easy!

Post a Contest Now or Join us Today!