Question description:
This user has given permission to use the problem statement for this
blog.
Filled cell should autofill formula. So if B5 has is shaded, filled with a number or check it will provide a x1.2 to the formula for the end result
Solved by K. U. in 22 mins
This is the chat thread from the real Excelchat help session. It contains no private user
information.
Excelchat Expert
08/02/2018 - 02:37
Hello, I understand that if a cell is filled, you want it multiplied by 1.2, right?
User
08/02/2018 - 02:37
It's an example.
Excelchat Expert
08/02/2018 - 02:37
Okay, can you provide a sample data set using the document preview please?
Excelchat Expert
08/02/2018 - 02:37
Before we proceed, this is a reminder that our policy is 1 question per session so for this session we'll be resolving this particular question.
User
08/02/2018 - 02:38
Okay.
Excelchat Expert
08/02/2018 - 02:39
Please provide a sample data set using the document preview. This is necessary because depending on your sample data, your request may only be partially be answered.
Excelchat Expert
08/02/2018 - 02:39
For instance, a shaded cell cannot be detected by Excel using formulas.
Excelchat Expert
08/02/2018 - 02:39
A checked cell can be but it depends on the kind of checked used.
User
08/02/2018 - 02:40
What I want to know is can I can check Well above target in any row and have it autofill a formula in C15?
User
08/02/2018 - 02:40
Does that make sense?
Excelchat Expert
08/02/2018 - 02:41
I'll need more information.
Excelchat Expert
08/02/2018 - 02:41
First, where will it check if a cell is above target?
Excelchat Expert
08/02/2018 - 02:41
Second, what is the formula in C15?
Excelchat Expert
08/02/2018 - 02:42
If you can provide actual numbers in your sample this would go a lot smoother.
User
08/02/2018 - 02:42
Sounds good
User
08/02/2018 - 02:43
So now for the formula
User
08/02/2018 - 02:44
I would want it to auto fill so target would be x1, AT x1.2, WAT x1.4
User
08/02/2018 - 02:44
So the result would read 18% x whichever cells are filled. Does that help?
Excelchat Expert
08/02/2018 - 02:45
Okay, so 18 is the expected output?
Excelchat Expert
08/02/2018 - 02:45
May I know how 18% was calculated?
Excelchat Expert
08/02/2018 - 02:45
Once I understand the logic of how you came up with 18% from the sample you wrote then I'll be able to write a formula for you.
User
08/02/2018 - 02:46
No, the formula would read 18% x 1 x 1.2 x 1.4
User
08/02/2018 - 02:46
The 18% is an internal number we have evaluation.
Excelchat Expert
08/02/2018 - 02:46
Okay, so 18% is a given.
User
08/02/2018 - 02:46
Yes.
Excelchat Expert
08/02/2018 - 02:46
Now, where did 1 come from?
User
08/02/2018 - 02:46
1 came from the x in target
Excelchat Expert
08/02/2018 - 02:46
Which X?
User
08/02/2018 - 02:46
On the sheet.
Excelchat Expert
08/02/2018 - 02:46
I see several Xs
User
08/02/2018 - 02:46
So for this result the end formula would be:
Excelchat Expert
08/02/2018 - 02:47
How do you know that one of these X is equal to 1?
User
08/02/2018 - 02:47
18% x 1 x 1.2 x 1.2 x 1.2 x 1.4 x 1.4
User
08/02/2018 - 02:47
T = 1, AT = 1.2, WAT = 1.4
Excelchat Expert
08/02/2018 - 02:47
Okay, so Target = 1?
User
08/02/2018 - 02:47
Yes.
Excelchat Expert
08/02/2018 - 02:47
There, that's the information I needed.
Excelchat Expert
08/02/2018 - 02:47
Can you press Enter in the document preview please to complete it?
Excelchat Expert
08/02/2018 - 02:47
You are still editing one cell.
Excelchat Expert
08/02/2018 - 02:49
So do you need the result of 0.6096384
Excelchat Expert
08/02/2018 - 02:49
0.6096384 is the result of 18% x 1 x 1.2 x 1.2 x 1.2 x 1.4 x 1.4
Excelchat Expert
08/02/2018 - 02:49
Do you need to see 0.6096384 in C15?
Excelchat Expert
08/02/2018 - 02:51
Are you still with me?
Excelchat Expert
08/02/2018 - 02:55
If you are still there, I've provided the solution to your question in C15.
Excelchat Expert
08/02/2018 - 02:55
This solution is based on the information I've gathered from you.
Excelchat Expert
08/02/2018 - 02:56
I could use some more information for you to create a better formula but since you are no longer replying, I'm unable to do that.
Excelchat Expert
08/02/2018 - 02:56
Hi, I have not received a reply from you for quite some time. I'm afraid the session will end automatically soon. I sincerely hope we were able to address your concern.
Excelchat Expert
08/02/2018 - 02:58
Here's the formula for your reference:
Excelchat Expert
08/02/2018 - 02:58
=rept("1 x",countifs(D5:D10,"x")) & rept("1.2 x",countifs(E5:E10,"x")) & rept("1.4 x",countifs(F5:F10,"x"))
Excelchat Expert
08/02/2018 - 02:58
I'll be ending the session now.
This is the output file from the real Excelchat help session:
This is an example of the expert help you can get. It contains no private user
information.
