excel formula help please

Question

I need to get this formula to work to calculate the hours worked&nbsp;I am wanting the last column to read the lower of the previous 2&nbsp;or 37.5 if the previous one is greater&nbsp;1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 51.50&nbsp;1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 37.50&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 45.50&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 37.50&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 31.00&nbsp;0&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 37.50&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 31.00&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 37.50&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 31.00&nbsp;0&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 37.50&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 31.00&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 7.50&nbsp;&nbsp;

riny_van_eekelen · Answer

peterpeter1780&nbsp;Don't follow the logic for the last row in your example, but it's probably a typo. Try this:Though it seems to be a meaningless formula as the result will always be 37.50&nbsp;

peterpeter1780 · Answer

I am trying to if b is 45.5 then it need to return 37.5 in row 2 its 31 so c needs to show 31 and not 37.5

riny_van_eekelen · Answer

peterpeter1780&nbsp;Then I misunderstood. Forget about the MAX part then and just keep&nbsp;=MIN(A1:B1)&nbsp;

peterbartholomew1 · Answer

peterpeter1780&nbsp;Sorry, but I have difficulty understanding what any of your number repressent or what you are trying to calculate.&nbsp; Also, it would be interesting to know what version of Excel you are using.&nbsp;&nbsp;&nbsp;With anything after 2007, the data should probably be within a Table and simple formulas would be written to the Table.&nbsp; For exampleStandardHrs
= MIN([@nominal],[@actual])

AdditionalHrs
= [@actual]-[@standard]With 365, the calculation becomes more demanding, but the result applies to the entire table and is dynamic= LET(
      standard,   MAP(nominal, actual, LAMBDA(x,y, MIN(x,y))),
      additional, actual - standard,
      HSTACK(standard, additional)
  )&nbsp;&nbsp;

peterbartholomew1 · Answer

Sorry for the overlap. I had put together a demo file and then got distracted by a discussion on solar panels before hitting 'Post'.

Forum Discussion

excel formula help please

6 Replies

Resources

51.50	1	37.50	45.50	37.50
31.00	0	37.50	31.00	37.50
31.00	0	37.50	31.00	7.50