Forum Discussion
Formulas
I need to calculate daily totals based on the following
<3,35 hours at 20
>=3,36 and <=4,45 hours at 40
>=4,46 hours at 60
Thanks for the help
Antonio
Antonio-
After reviewing your file I believe one of these formulas will work:
IF Formula: =IF(B4<=3.35,20,IF(B4<=4.45,40,60))
IFS Formula: =IFS(B4<=3.35,20,B4<=4.45,40,TRUE,60)
**IFS is applicable for: Excel for Office 365 Excel for Office 365 for Mac Excel 2016 Excel 2016 for Mac Excel Online Excel for iPad Excel for iPhone Excel for Android tablets Excel for Android phones Excel Mobile
Please see the attachment for reference.
This formula that John Jairo Vergara Domínguez put together should work:
=SUMPRODUCT((A1>{0;3.35;4.46})*(A1-{0;3.35;4.46})*{20;40;60})
Note: You may need to change out the periods with commas for the values. I have a US version of Excel. It looks like you may have a Spanish version?
Please reference this thread for an example file and a detailed description of how this formula works:
https://techcommunity.microsoft.com/t5/Formulas-and-Functions/Tricky-3-Part-Formula/m-p/196544#M5689
I Matt
I 'll send the data sheet that I need
Thanks
António
Antonio-
After reviewing your file I believe one of these formulas will work:
IF Formula: =IF(B4<=3.35,20,IF(B4<=4.45,40,60))
IFS Formula: =IFS(B4<=3.35,20,B4<=4.45,40,TRUE,60)
**IFS is applicable for: Excel for Office 365 Excel for Office 365 for Mac Excel 2016 Excel 2016 for Mac Excel Online Excel for iPad Excel for iPhone Excel for Android tablets Excel for Android phones Excel Mobile
Please see the attachment for reference.
