Calculate SUM of Row with 5% Less

[Nitin Shardul]

Hello Sir,

Please find attached file for more clarity.

Thanks,

Nitin

 

[Somendra Misra]

Hi Nitin,

Can you explain the logic of your problem, how you are expecting 500 in E5??

Regards,

 

[Nitin Shardul]

Hi Nitin,

In E5 If B5:D5 is greater than 0 then 5% is deducted from the sum or Sum=B5:D5, Hence 500 in E5



Can you explain the logic of your problem, how you are expecting 500 in E5??

Regards,

 

[Somendra Misra]

@Nitin Shardul

Two Alternative:

1. =IF(SUMPRODUCT(--(B5:D5>0))=3,SUM(B5:D5)-(SUM(B5:D5)*5%),SUM(B5:D5)) --- Normal formula in E5 and copy down.

2.=IF(AND(B5:D5>0),SUM(B5:D5)-(SUM(B5:D5)*5%),SUM(B5:D5)) in E5 and copy down... this is array formula so must be entered with Ctrl+Shift+Enter.

Regards,

 

[Nitin Shardul]

Great
Thank You

 

[Hui]

Why use: SUM(B5:D5)-(SUM(B5:D5)*5%) in Somendras formulas
When: SUM(B5:D5)*95%
is the same

 

[Hui]

Heres a slight twist:
=SUM(B5:D5)*(1-(5%*(SUMPRODUCT(--(B5:D5>0))=3)))

 

[Somendra Misra]

Sure Hui ... That' thing not came to my mind @ first instinct.

If values are always going to be +ve or 0 than here another option:
=IF(MIN(B5:D5),SUM(B5:D5)*0.95,SUM(B5:D5))

Regards,