Calculation or Formula error HELP...

HECGroups,

If I understand you correctly, try the following formula in cell P4 and let me know if it works:

Please Login or Register  to view this content.

Try changing your IF formulas to look for 0 instead of "", e. g., =IF(E5<> 0,SUM(B4*C4/100)+B4*E5,"")

thanks for replying. Could you please me to understand the formula.

SUMPRODUCT takes the first array, multiplies it by the corresponding number in the second array, and then adds the numbers together.

Using your specific case as an example:
The first array is D4:O4, so the amounts in those cells make up array one.
The second array is: (16-COLUMN(D4:O4)). This structure allows us to reduce the multiplied by 1 for each month. For example, 16-COLUMN(D4) is 16-4 = 12 (because column D is the 4th column). Similarly, 16 - COLUMN(E4) is 11, and so on...

So, if we assume you have 1 employee for each month, then each cell in array 1 will be 110. The values in array 2 are 12, 11, 10..., 1 The formula multiplies in order: Value 1 in array 1 * value 1 in array 2 (110 * 12) + value 2 in array 1 * value 2 in array 2, etc

DOes this help?

Excellent guidelines. thanks for your help. If any one ask me i am able to explain the formula this is the reason i have asked for explanation. don't mind.

I'm the same way. Learning the formula syntax also helps you reapply it in different settings.

I'm happy I could help.