Excel Function

Posted on 2011-04-21
Last Modified: 2012-05-11
I work for a company that completes Service Orders as a task and the team needs to complete atleast 30 S.O to receive money.

I need a function in excel that computes the math.

So if the number is > than 30 then 30/6 where 6 is the number of working days which is equivalent to 5/day then 5*30 is the amount they receive which is 150. I need a function to compute the math to output 150$
Although if the number of input is 31 then 31/6 is 5.1666 which should be rounded to 5

Another function similar to above should be used
Question by:Moizsaif123
    LVL 11

    Expert Comment

    Moizsaif, sorry your question isn't very clear, especially the bit about "another" formula.

    When do you round up, if ever? What happens if the SO total gets to say 50?
    LVL 4

    Author Comment

    round up only on the division part of the function portion so say if total is 50 then 50/6=8.33 rounded up to 8

    My condition states for a total below 30 should not be considered and the output is 0.
    LVL 11

    Accepted Solution


    Where A1 holds the number of SO's
    LVL 2

    Expert Comment

    let's assume u have your SO count in cell "A1"

    Formula 1:
    then the function should read" =rounddown($A$1/6,0)*30

    Formula 2:

    let me know if it helps!!!


    Featured Post

    Free Trending Threat Insights Every Day

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    Join & Write a Comment

    Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Dat…
    How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
    The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
    This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

    730 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

    16 Experts available now in Live!

    Get 1:1 Help Now