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 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

Answer 2

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:

=IF(attenDance>minDays,

   SUM( verifiedOutPut - excessOutPut) \* standardPrice + 

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

            IF(attenDance>0, 

         SUM(verifiedOutPut-excessOutPut)\*standardPrice+ 

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

IF(attenDance<=0,0)))

hi guys for some reason i edited the second IF condition and somehow the results i expected has been delivered.

a sample below:

Answer 3

here is a link to the book please,

https://1drv.ms/x/s!AlF293aweqwLlTneYqpXqlJowQCZ?e=rptimO

Answer 4

Can you share a sample data of your workbook?

aww, thank you i was just uploading an image when ur reply came in. will this be fine or i should share the book still

Answer 5

Can you share a sample data of your workbook?