Posts by onexc


    I want to round up time (time after calculating differences of two times) the nearest whole value as hour(s), but if it equals or greater 30 minute(s), this should be also rounded up to nearest hour. For instance, difference between time A and time is 1 hour and 30 minutes. Then, difference should be rounded up to 2 hours, not 1 hour. Here is the formula I am dealing with:

    1. =ROUNDUP(INT((A2-B2)*24),0)

    Thanks in advance.


    I would like to allocate my budget (B11 in the sample) through items to be sold. I am stuck with fixed percentages given by product seller; a price to be offered cannot be out of minimum and maximum percentage values. My percentage will be set according to this formula: Price (B7, for instance) / Total Budget. My boss wants to arrange ranges freely in accordance with minimum-maximum percentage rates and she wants to know if a value entered into any item price, how much percent it is to minimum and maximum percentages and what price should we put into the cell in order to stay between fixed percentage values as minimum and maximum. Although it seems that like simple, for me, it is an issue to overcome. Any help will be appreciated.


    • Sample.xlsx

      (9.6 kB, downloaded 42 times, last: )


    I am trying to calculate minimum and maximum quantity price per items for a tender offer. My problem is there are different metrics in my Excel sheet. It is easy to calculate prices per items because they are simply quantities. However, when it comes to cubic meters, I do not know where to begin. For instance, what is one item/unit price for 1.3 tonnes. If I divide 5,000 dollars to 1.3, the result is 3.846 . Seemingly this is not what is desired as a unit price. Any help would be appreciated.


    I need your help that couldn't handle. I have a table displaying seller names. I built up a formula in the same table showing sellers to success, not success, success-late, and pending (same sellers' names exist but product is the same). When I created a Pivot Table, I want to also display different percentages of seller. For instance, normally a seller who sold 6 items give me %100. Because what target is determined by selling number. However, some sellers sold items after deadline (success-late), but the same seller sold another item successfully (success), and one item is still pending.

    I want to create a slicer in order to display different percentages. For example, Seller X's success rate must be divided to its successfully item's to success-late plus pending.

    How can I overcome this. I know Pivot Table restricts some multiple calculations because my formula in the same column of the table.

    Thanks in advance.

    Hello Carim,

    you have offered a very perfect solution as always. You are exactly right; I chould have added desired results into the sheet. I have just downloaded your modified sample. You are nearly correct to the result. instead of number of uniques senders, who is the one who was given 4 rating by the same customer most.

    For instance, Company E was given 4 by Customer A for 3 times ; Company F was also given rating 4 by Customer C for 2 times..; Company E was given 4 by by Customer B for 5 times.

    Desired list should display this:

    Company A - 5
    Company E - 3

    Because We look at the number of given value by the same customer.

    Even if Company E was given rating 4 by several customers and become "the company that most rating 4 given". it is not important for us, we make this list to "the company that most rating 4 given by the same customer".

    Hello Ozgrid,

    Maybe it's not so complicated but I couldn't overcome the problem anyhow.

    In the file as attached, three rows; Sender Company, Receiver Customer, and Delivery Rating. This is what I want to reveal that let me explain;

    We will count only rating 4 given to sender by receiver; however, I want to know who is the customer which gave rating 4 to the same sender company. For instance; Receiver Customer A gave rating 4 to Sender Company F for 6 times.

    I need a list beginning from the largest to the smallest receiver to the count of rating for But "regarding the same sender customer".

    For example, Receiver customer B gave rating 4 to companies for 8 times in total, but the first customer in the list who gave rating 4 is still "Receiver A". Because they gave rating 4 to a sender company for 6 times.

    I hope it is clear.

    Thank you in advance.



    perhab like this
    if you have format text not : ex / you can join timevalue + substitute

    Hi Graha,

    I tried a very similar solution to yours, probably it's almost close to what I want to see but it's a little bit complicated in my case. Because I don't want to use 1904 system as well as displaying time span as text for some reasons. Regarding your sample, results are in A3 and A2 both are calculated separetly which their values come from another cells. And I am trying to make another calculation in another cell over A3 and A2. Though I'll try your instance again with another approach. Thank you much.

    Hi OzGrid,

    Although it may be little bit complicated on the topic title, it's something that I have been working on it for days. Excel can calculate negative time span values under certain circumstances if I am correct: 1904 system or converting to a text. At the beginning, I fully rejected to approach these particular solutions. Instead, I built up a formula to break the time into pieces by twos with LEFT, MID, RIGHT as if they are numbers separated with colon (like a modem's mac address) then applied a value. It worked! Subtracted one from another... Negative value appeared as I expected (I have to tell you that I also used a customer format 00\:00\:00.

    However, it does not work when It comes to cells contain calculated span time values.

    A2 contains this: 00:20:15 this comes from with INT formula and subtraction E2 and F2 both contains date with time.
    A3 contains this: 00:10:15 this comes from another cell G2 (a span time; hours, minutes, seconds) plus TIME(0,3,30)

    I want A4 to display subtraction A3-A2... Because neither A2 nor A3 is text. They are [hh]:mm:ss

    I'd like to apply a trick... In A4, how can I convert them to text first, then apply a subtraction calculation and display the result as a value like they are numbers in the same formula without helper or VBA.

    Your help will highly be appreciated on this mixed problem. Thanks in advance.

    Hi Carim,

    I do really like your approach to questions. I just assume it may be related a TEXT function based solution but... regarding your question, yes I prefer to be close to a formula with WEEKDAY so that i can change deadline just by changing day number laterly.