Nested IF( ) function formula help needed

Question

hi have been trying this on my design but the final result hasnt been as i expected

=IF( attenDance>minDays,

   SUM( verifiedOutPut - excessOutPut) \* standardPrice + 

         SUM( excessOutPut \* pricePerExcessClaim) + maximum\_TNT, 

            IF( attenDance<maxDays, 

         SUM( verifiedOutPut-excessOutPut)\*standardPrice+ 

  SUM( excessOutPut\*standardPrice)+minimum\_TNT/minDays\*attenDance, 

IF( attenDance<=0,0)))

per my evaluation the last part isnt given me the 0 i expect when the atttendance cell = 0.

thank you for reading

screen sample is below:

Answer 1

Hi there

Glad to help you.

If the answer helped you.

Please, consider marking this thread as answered.

It would help others in the community with similar questions or problems.

Thank you in advance

Regards

Jeovany

Answer 2

BTW

You may find a copy of your workbook in this link with the updated formula and a different macro to submit the values. (I hope you don't mind)

https://we.tl/t-wC20nZKIjC

hi Jeovany thank you so much for help, well appreciated.

Answer 3

BTW

You may find a copy of your workbook in this link with the updated formula and a different macro to submit the values. (I hope you don't mind)

https://we.tl/t-wC20nZKIjC

Answer 4

Hi JackAmin

It is recommended to first evaluate the 0 (zeroes) and "Blank " output when dealing with IF statement/logical formulas.

Please, try the formula below.

=IF(attenDance<=0,0,IF(attenDance>minDays,

SUM( verifiedOutPut - excessOutPut) * standardPrice +

SUM( excessOutPut * pricePerExcessClaim) + maximum_TNT,

IF(attenDance<maxDays,

SUM(verifiedOutPut-excessOutPut)*standardPrice+

SUM(excessOutPut*standardPrice)+minimum_TNT/minDays*attenDance,0

)))

Please, also notice that cell M10 is Custom formatted to show a dash " - " when the result is 0.

I hope this helps you and gives a solution to your problem

Do let me know if you need more help

Regards

Jeovany

Thank you Jeovany.

its worked perfectly. thank u always