# Thread: Having the option of which input will effect calculations

1. ## Having the option of which input will effect calculations

The attached spreadsheet shows that there are calculated cells in Column K, P & V. The input columns are Q & W. I would like to either input the whole number in the respective cell in column Q or the % number in the respective cell in column W. How am I able to accomplish this on the worksheet?

Thank you.

2. To keep things simple and easy to debug, I would add a helper column with a formula something like this (for row 6 as an example):

Code:
=IF(AND(Q6<>"",W6<>""),"Error: enter data in one place only",IF(AND(Q6="",W6=""),"No data",IF(Q6<>"",Q6,W6)))
Play around with this to get the exact outcome you are looking for, and then use the helper column as the input for your subsequent calculation.

3. This works just fine. Many Thanks

4. Fukuoka,

Again thanks for the assistance. I was looking at the formula and wondering exactly how it works. Could you please break it down into sections and explain it.

=IF(AND(Q6<>"",W6<>""),"Error: enter data in one place only",IF(AND(Q6="",W6=""),"No data",IF(Q6<>"",Q6,W6)))

Thanks again

5. An IF function has three parts:

1. The test you are applying
2. What to display if the test is TRUE
3. What to display if the test is FALSE

The example I gave uses nested IFs, which I hope you can follow in this explanation . . . .

The first test applied is looking to see if there is data in BOTH Q and W: this is written as
AND(Q6<>"",W6<>""). If both Q6 isn't blank [Q6<>""] and W6 isn't blank [W6<>""], then the test is TRUE, and the cell will display "Error: enter data in one place only".

But if they are not both blank, ie the test is FALSE, go on to another IF statement where the test is whether both W6 and Q6 are blank, written as
AND(Q6="",W6="").

If they are both blank then the test is TRUE and
"No data" is displayed.

But if one of them is not blank the test is FALSE and we go on to the third nested IF test. This last one asks whether Q6 isn't blank: [
Q6<>""]. If that's TRUE then display Q6. If its FALSE then it must be W6 that isn't blank, so that is displayed.

Hope this helps.

PS Fukuoka is the city where I am !

6. THANKS but how does the IF(AND) work together.

and sorry about the name vs city.. by the way where is Fukuoka located

7. MNN,

The And() allows you to do multiple tests, all of which must be true for the IF to be true.

8. Fukuoka is at the West end of Japan, quite close to South Korea.

9. Thank you both.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•