SOLVED

Excel formula assistance

%3CLINGO-SUB%20id%3D%22lingo-sub-3188896%22%20slang%3D%22en-US%22%3EExcel%20formula%20assistance%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3188896%22%20slang%3D%22en-US%22%3E%3CP%3EI%20am%20trying%20to%20get%20an%20output%20in%20percentage%20from%20list%20of%20rows.%20Its%20like%20if%20sum%20of%20the%20value%20in%20row1%20to%20row4%20is%20100%25%2C%20then%20return%200%25%20or%20subtract%20100%25%20with%20the%20sum%20of%20values%20in%20those%20rows%20or%20I%20wanted%20to%20pick%20a%20value%20in%20a%20specific%20row%20I15%20and%20return%20that%20value.%3C%2FP%3E%3CP%3E%3CBR%20%2F%3EI%20tried%20this%20formula%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel-formula%22%3E%3CCODE%3E%3DIF(OR(SUM(I14%3AI17)%3D100%25)%2C0%2C100%25-SUM(I14%3AI17))%20%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26amp%3B%20its%20working%20before%20adding%20the%20last%20part%20(I15%26gt%3B10%25)%2CI15.%3C%2FP%3E%3CP%3E%3CBR%20%2F%3EWhen%20I%20add%20the%20last%20part%20(as%20mentioned%20below)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel-formula%22%3E%3CCODE%3E%3DIF(OR(SUM(I14%3AI17)%3D100%25)%2C0%2C100%25-SUM(I14%3AI17)%2C(I15%26gt%3B10%25)%2CI15))%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20getting%20error%20-%20%22%3CU%3EYou%20have%20entered%20too%20many%20arguments%20for%20this%20function%3C%2FU%3E%22%3C%2FP%3E%3CP%3E%3CBR%20%2F%3EAny%20idea%20how%20to%20fix%20it%20or%20is%20there%20another%20approach%20to%20get%20result.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3188896%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3189016%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20formula%20assistance%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3189016%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1276444%22%20target%3D%22_blank%22%3E%40mlgroms%3C%2FA%3E%26nbsp%3BThe%20error%20message%20meant%20that%20you%20incorrectly%20constructed%20the%20formula.%20It's%20a%20combination%20of%20using%20OR%20and%20the%20placement%20of%20the%20right%20brackets%20%22)%22.%20It%20just%20didn't%20line-up%20as%20it%20should.%20Nevertheless%2C%20it%20seems%20that%20the%20formula%20below%20does%20what%20you%20ask%20for.%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel-formula%22%3E%3CCODE%3E%3DIF(I15%26gt%3B10%25%2CI15%2C1-SUM(I14%3AI17))%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3EIn%20words%2C%20if%20I15%20is%20greater%20than%2010%25%20then%20return%20the%20value%20in%20I15%20otherwise%20calculate%201%20minus%20the%20sum%20of%20I14%3AI17.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3189030%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20formula%20assistance%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3189030%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1276444%22%20target%3D%22_blank%22%3E%40mlgroms%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20first%20part%20is%20clear%2C%20but%20adding%20the%20second%20part%20is%20not%20clear.%3C%2FP%3E%3CP%3EI%20believe%20this%20is%20the%20formula%20you%20want%2C%26nbsp%3B%3C%2FP%3E%3CP%3E%3DOR(IF(OR(SUM(I14%3AI17)%3D100%25)%2C0%2C100%25-SUM(I14%3AI17))%2CIF((I15%26gt%3B10%25)%2CI15))%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
New 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 (New 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.