• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Combining Two Separate Functions within One Formula

C

cmkarnes

Member
I have an extremely large Excel document - In one of the sections of this document, there are eight questions, and the user can choose via check box either Low, Medium, High, for the answer to each question. There are numerical values assigned to each checkbox. In another part of the document, I have a cell that reflects the total, and next to this cell, a cell reflecting a percentage based on the total score divided by the number of questions.

What I would like to do is this: For the cell that reflects the percentage, I would like to override the formula should the user answer a totally separate question (not related to the eight questions mentioned above) via a Yes/No dropdown. If the user answers "Yes" I would like the percentage to automatically default to 45%, but if the user answers "No" I would like the percentage formula to calculate as normal. I am unable to download a sample due to the sensitive nature of the document, as well as its size, so made a mocked up sample below:

TOTAL POINTS (Cell A10) AVERAGE (Cell B10)
(based on total from the 8 questions) (formula calculates % based on Cell A10)

Cell A2 would be the Yes/No Dropdown - if they choose "Yes" need the average to default to 45% - if they choose "No" the formula in place calculates normally.

Thank you - cmk
 
CMK

Can you post a sample file as it is difficult to know exactly what you want

Highlight the two conditions and appropriate answers
 
Hi ,

Would it not be an IF statement on the lines of :

=IF(A2 = "Yes" , 0.45 , A10/8)

Format the cell where this formula is entered as Percentage.

I assume that the maximum value that A10 can have is 8 , which will then result in an output of 100%.

Narayan
 
Hello to you both, sorry for the late acknowledgement of your replies - severe storms & house hit by lightning, which destroyed the internet carrier cables and such. I'm back on line as of this morning, so while at work, will try these out. I will let you know what happens in case I have issues. thank you - cmk
 
Hello, have been playing around with the suggested formula - can't seem to get it to work. The first argument does, where 45% is automatically populated if in a different part of the document the user chooses "Yes" from a dropdown - what I can't seem to solve is the 2nd argument, where if the user answers "no" the default formula in the cell applies. I get the #Value! error, and it won't calculate.

Unfortunately, I cannot download the document because of security issues. anyone have suggestions? thank you - cmk
 
Figured it out - this is what works (posting in case anyone else runs into the same issue):
=if((or(A2="yes",0.45)),A10/8)

thank you again!
 
You must log in or register to reply here.
Back
Top