SOLVED

Excel formula assistance

Copper Contributor

I am trying to get an output in percentage from list of rows. Its like if sum of the value in row1 to row4 is 100%, then return 0% or subtract 100% with the sum of values in those rows or I wanted to pick a value in a specific row I15 and return that value.


I tried this formula

 

 

=IF(OR(SUM(I14:I17)=100%),0,100%-SUM(I14:I17)) 

 

 

& its working before adding the last part (I15>10%),I15.


When I add the last part (as mentioned below)

 

 

=IF(OR(SUM(I14:I17)=100%),0,100%-SUM(I14:I17),(I15>10%),I15))

 

 

I am getting error - "You have entered too many arguments for this function"


Any idea how to fix it or is there another approach to get result.

4 Replies
best response confirmed by mlgroms (Copper Contributor)
Solution

@mlgroms The error message meant that you incorrectly constructed the formula. It's a combination of using OR and the placement of the right brackets ")". It just didn't line-up as it should. Nevertheless, it seems that the formula below does what you ask for.

=IF(I15>10%,I15,1-SUM(I14:I17))

In words, if I15 is greater than 10% then return the value in I15 otherwise calculate 1 minus the sum of I14:I17.

 

 

Hi @mlgroms 

 

The first part is clear, but adding the second part is not clear.

I believe this is the formula you want, 

=OR(IF(OR(SUM(I14:I17)=100%),0,100%-SUM(I14:I17)),IF((I15>10%),I15))

 

 

Thanks @Riny_van_Eekelen, your formula worked fine. Appreciate your assistance.

Thank you @Jihad Al-Jarady, I tried to use your formula, I am getting value "True" but not the calculated numbers. Appreciate in looking into this.

1 best response

Accepted Solutions
best response confirmed by mlgroms (Copper Contributor)
Solution

@mlgroms The error message meant that you incorrectly constructed the formula. It's a combination of using OR and the placement of the right brackets ")". It just didn't line-up as it should. Nevertheless, it seems that the formula below does what you ask for.

=IF(I15>10%,I15,1-SUM(I14:I17))

In words, if I15 is greater than 10% then return the value in I15 otherwise calculate 1 minus the sum of I14:I17.

 

 

View solution in original post