1. A

    Need minimum value in a SUMPRODUCT formula

    I have a database that shows a bunch of courses required by my employees and then a column for every week in the year to track at which point the employees attend the course and get credit for it. At any given week I want to know the percentage of students that graduated from class as an...
  2. T

    INDEX, MATCH, MIN formula on non-contiguous range

    Hi, Looking for assistance with a formula. My best guess is it will use Index, Match & Min. I want to look in this range and find the Minimum value. =MIN(F3,H3,J3,L3,N3) I then want to return a column header for that value. Headers are in Row 2 This is also from a larger set of data if that...
  3. S

    OFFSET query

    Hi, I have a formula that's working perfectly for me, which is this... "=IF($C4=0,"",MIN(IF('Main Log'!$D$4:$D$2000=$B4,IF('Main Log'!$O$4:$O$2000,"",'Main Log'!$E$4:$E$2000)))) It finds the minimum value (earliest date) in my 'Main Log' sheet where the criteria are met. However, in the next...
  4. U

    Need help finding the account with the lowest and highest value from several sheets.

    I am trying to return the account number with the lowest and highest values. I tried to use this formula to return the lowest value. =MIN(AT3,BJ3,BZ3,CP3,DF3,DV3,EL3,FB3,FR3,GH3,GX3,HN3,ID3,IT3,JJ3) =MAX(AT3,BJ3,BZ3,CP3,DF3,DV3,EL3,FB3,FR3,GH3,GX3,HN3,ID3,IT3,JJ3) Account # Column 206-1...
  5. DragonJad

    Remainder to be Subtracted from the next Instance Found

    Hi All, I'm having difficulties figuring out what function to use in the OUT column (B) to calculate the following: If the number in OUT (G) is greater than the TOTAL C then it should only show the MAX of TOTAL (C) otherwise subtract the remainder from the next instance of the colour (A) Blue...
  6. jase71ds

    An Elegant Solution to Setting Evaluation Constraints

    This isn't a question - just wanted to share a solution I came up with. Others have probably come up with the same solution, but I was pleased with myself for figuring it out on my own. Plus, it was fun! If I want to constrain a formula output, say between +100% and -100%, one popular way to do...
  7. A

    Use of LARGE, SMALL, MAX, MIN, SUM Functions in spilled arrays

    Hi all, I would like to use functions like LARGE, SMALL, MAX, MIN and SUM in spilled arrays and get the values PER row instead of the total. For Example: x y =MAX(A2:B2) dragged down =MAX(A2:B5) 1 2 2 8 3 4 4 5 6 6 7 8 8 The only solution I...
  8. R

    Use LARGE function with non-numerical data

    I need to pick up the lowest value out of two highest out of three. So I have now 3 non-numerical values, which correspond to numerical values and I need to choose the lowest out of the two highest. I figured out that I can use LARGE to pick the 2 highest values and then use MIN to pick up the...
  9. A

    Find days between two dates

    Dear All, I am looking for a simple formula, which will display the number of days in a year. Kindly help me. Name DOJ EOS 2013 2014 2015 Employee 1 01-01-13 08-08-15 364 364 220 Employee 2 05-04-14 31-05-16 270 364 Employee 3 15-02-14 16-04-15 319 106
  10. alm395

    Count Consecutive Text in Rows (MIN, MAX, AVG)

    I have a table with employees down column A and dates going across (3/20/20 - 4/4/21). Each date is tracked with that person's daily status. I already have a section that counts the totals of each status type, but am now looking to find the min, max, and avg of consecutive dates with the...
  11. S

    INDEX / MATCH Over multiple pages

    I have a work book that I'm trying to use to store supplier prices for 1-15 products over certain postal codes 1-99999999? currently set out like table below The postcodes are in the B column from 35 down, the products are in 34 C across currently stopping at L but possibly more to be added PAGE...
  12. D

    MIN&MAX works for 18/19 but not 19/20

    I have a spreadsheet that for each item calculates the number of days that fall within the financial year and then the cost that would relate to it. The formula is: =I10*((MAX(MIN($Q$1,H10)-MAX($P$1,G10)+1,0))/365) Where:- P1: first date in the current financial year being reported -...
  13. C

    VBA to insert Min/Max in cell

    Hi, I need to insert formulas using VBA and all work but the Min and Max formulas. This formula when entered directly into a worksheet cell with the formula bar works as it should: =IF(MAX(REPORT!G2:G21)=0, ", MAX(REPORT!G2:G21)) But when entered using VBA it is not recognized as a formula...
  14. S

    Min and Max Functions returning a 0 value when looking up a formula

    Hi, I have the following formulas in cells FQ3 and FR3 which return the numbers 16 and 19 repsectively =IFERROR(LEFT(INDIRECT(FQ$1&$DZ3),(FIND("-",INDIRECT(FQ$1&$DZ3),1)-1)),"") =IFERROR(RIGHT(INDIRECT(FQ$1&$DZ3),(FIND("-",INDIRECT(FQ$1&$DZ3),1)-1)),"") However I then want a formula that...
  15. D

    Min and Max in sequence

    Good day, I have two columns of data, 1st is time and the second are values, The time column is from 4.00am to 4.00am the next day increasing by 1hour intervals, the values increase to a max and then decline in a series of highs and lows. I want to calculate the change in value meaning the...
  16. D

    #N/A error with Lookup and Min functions

    Good Afternoon everyone, I have run into an issue using Lookup with Min. I can use Lookup with Max and get the required results, =LOOKUP(MAX(E7:E1000),E7:E1000,D7:D1000) actually gives the date that matches the Max value. When I change the formula to =LOOKUP(MIN(E7:E1000),E7:E1000,D7:D1000), it...
  17. gtd526

    Finding Multiple Shapes

    I can't find the 2nd or 3rd Shape I'm want to edit. It locates the 1st one, but not the 2nd or 3rd. I used "record macro" to select the shapes and get the name. Sub PleaseFind() ActiveSheet.Shapes("Rectangle 9").Select ActiveSheet.Shapes("Rectangle 5").Select ActiveSheet.Shapes("Rectangle...
  18. Heber

    time calculation on working hours less weekend

    Hi I am Struggling to solve this! I need to calculate time between two dates, but need to be only between 8:00 AM and 5:00 PM, excluding weekends. I got some results, but when the start time is over 5:00 pm still add the time... example below: Start End Transmission time (h:mm) 28/09/2019...
  19. J

    textbox max of 2 and min of 1

    is it possible to input 1 and 2 only in textbox ?
  20. R

    Min formula with 2 criteria

    Hello, I need a simple MIN formula with 2 criteria. Below Is the table <tbody> component description qty a systematic 10 d non-systematic 62 c systematic 84 b systematic 56 d systematic 3 b non-systematic 1 a non-systematic 9 c systematic 84 b...

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back