1. 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...
  2. 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...
  3. 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...
  4. 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
  5. 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...
  6. 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...
  7. 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 -...
  8. 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...
  9. 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...
  10. 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...
  11. 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...
  12. 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...
  13. 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...
  14. J

    textbox max of 2 and min of 1

    is it possible to input 1 and 2 only in textbox ?
  15. 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...
  16. C

    Formula for Retirement Eligibility Date - min age (58) and years of service plus age is min of 70

    I want to start off with saying that I have scoured the internet to find this answer -- it's driving me crazy. Someone please help!! I need an excel formula that calculates a date based on: 1) Minimum age of 58 years 2) Age plus service is 70 or greater
  17. willow1985

    Combining 3 formulas (determine wk # max date, wk# min date, combine results)

    Is there a way to combine these 3 formulas? =MAX(DATE(T2,1,1),DATE(T2,1,1)-WEEKDAY(DATE(T2,1,1),2)+(S2-1)*7+1) =MIN(DATE(T2+1,1,0),DATE(T2,1,1)-WEEKDAY(DATE(T2,1,1),2)+(S2*7)) =CONCATENATE(TEXT(U2,"[$-409]D-MMM;@")," to ",(TEXT(V2,"[$-409]d-mmm;@"))) (T2 is the year and S2 is the week number)...
  18. J

    Filtering a Pivot by user input value?

    Hi, I have a pivot table with the left column numbers 1-100 And I want to be able to filter between 2 numbers (from user inputboxs for min and max) Eg. Numbers 11-20 or 1-80 Unsure how to do this, Any help appreciated
  19. S

    Score Dashboard in Excel

    Hi All, I am working on dashboard in which target based scores are being given. e.g Min & Max target for Calling - Minimum 60 call and Max >=70 calls per day Minimum score 0 and Maximum is 20%, Score will be proportionate where Achievement 61= Score will be 2% , Achv 62= Score 4% and so on...
  20. B

    Excel time formulas

    Hi, I'm looking for a simple formula that will calculate the difference between a start and an end time then subtract 30 min. So, for example, the start time is 7:00 am and the end time is 4:00pm. I can calculate the time difference but I don't know how to then modify the formula to subtract...
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

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