Help to make formula shorter.

[Felix]

Please, see the attached SS, and let me know how to shorten the formula from B6:H6. I know there might be a way, but I could not find it. I leave it to experts. I am learning, though, check the one I just used in E1.

 

[jeffreyweir]

In E1, instead of using this array formula:
=INDEX($B$6:$H$6,,MAX(IF($B$6:$H$6<>0,COLUMN($B$6:$H$6)-COLUMN($B$6)+1)))
...just use this non array formula
=MAX(B6:H6)

 

[Hui]

In E1 I would use:
=INDEX(B6:H6,,MATCH(0,B6:H6,0)-1)
as a Non-array formula

 

[jeffreyweir]

Whoops, for some reason I thought the numbers in C6:H6 would always increase. Not so. Ignore my feeble suggestion.

 

[Somendra Misra]

@Felix

You may also like to try below non-array formula:

=LOOKUP(2,1/(B6:H6<>0),B6:H6)

Regards,

 

[NARAYANK991]

Hi Felix ,

I may be wrong , but my reasoning would result in a different daily quota.

When nothing has been sold as on the first of July , you should still have a daily quota figure in E1 based on the weekly quota figure entered in A4.

When a figure for the day's sales is entered in B5 , the daily quota in E1 should reflect this ; thus , according to this , the correct formula in E1 should be :

=($A$4-SUM(B5:H5))/COUNTBLANK(B5:H5)

Narayan

 

[Felix]

Thank you all for the quick response. This time I replaced E1's with Narayan's.