Solved Formula needed to populate a single cell

November 4, 2011 at 11:56:17
Specs: Windows XP
I need to develop a formula which will let me change the value in F1 base on the following condition if user key in DONE on A1 it will show 10% on F1, if user key in DONE on B1 it will show 20% on F1, if user key in DONE on C1 it will show 30% on F1, if user key in DONE on D1 it will show 40% on F1, if user key in DONE on E1 it will show 50% on F1. Can someone help on showing me how to populate this formula? Thank you for your time.

See More: Formula needed to populate a single cell

Report •

✔ Best Answer
November 5, 2011 at 15:13:51
If you needed something other than what you asked for, why didn't you ask for it? By trying to simplify the question, you ended up making more work for those that are trying to help you.

Try this formula in K1:

=CHOOSE(COUNTIF(A1:J1,"DONE")+1,"",0.5,0.1,0.15,0.2,0.25,0.3,0.45,0.7,0.7,1)

For the Data Validation try this:

1 - Select B1:J1
2 - Data...Validation...Custom
3 - =A1="DONE" (the formula will automatically update to look "left" 1 cell for each cell in the range B1:J1)
4 - Uncheck "Ignore Blank" since we don't want it to ignore blank cells
5 - Click the Error Alert tab
6 - Enter a message for your users

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.



#1
November 4, 2011 at 12:49:14
What if the user keys in DONE in more than one cell? What do you want to see in F1?

What if the user doesn't key in DONE in any cell? What do you want to see in F1?

BTW...you appear to be looking for a Nested IF statement. You can find info on Nested IF's in both the Excel Help files and via Google.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
November 4, 2011 at 13:15:26
What if the user keys in DONE in more than one cell? What do you want to see in F1?
I would like to see the latest DONE for example that I given if A1 till E1 is being keys in with DONE than it will show 50% on F1 assuming that user is following process A1,B1,C1,D1,E1.
But if user were to key in A1 & B1 with DONE than he skip C1 and key in D1 with DONE can we still show as 20% on F1 as user is not following the process already?

What if the user doesn't key in DONE in any cell? What do you want to see in F1?
If the user doesn't key in DONE in any cell, F1 will show nothing which is blank.


Report •

#3
November 4, 2011 at 13:48:48
re: "I would like to see the latest DONE, F1 should be blank if no DONE's have been entered..."

This brute force Nested IF formula will give you the values you want, assuming the user has followed the process:

=IF(E1="DONE",50%,IF(D1="DONE",40%,IF(C1="Done",30%,
IF(B1="DONE",20%,IF(A1="DONE",10%,"")))))

This more elegant formula should also give you the results you want, but again assuming the user has followed the process.

=COUNTIF(A1:E1,"DONE")*0.1

re: "But if user were to key in A1 & B1 with DONE than he skip C1 and key in D1 with DONE..."

Dealing with users that skip cells can be difficult to handle via a formula.

If it were me, I would use Data Validation that would not allow the user to enter DONE into a cell unless the previous cell already contained DONE.

In other words, don't let them enter DONE on B1 unless A1 already contains DONE. Then don't let them enter DONE in C1 unless B1 contains DONE, etc.

If you do that, then the formulas above will always be accurate.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Related Solutions

#4
November 5, 2011 at 09:33:50
Thanks DerbyDad03 for the formula at first I thought that I can get away with it but seem like excel only limit me to 8 time of Nested IF formula only I actually have 10 cell that I need to validate. And there is a new limitation now which is the percentage is not in order of increase.
Below is what the situation look like
if user key in DONE on A1 it will show 5% on K1
if user key in DONE on B1 it will show 10% on K1
if user key in DONE on C1 it will show 15% on K1
if user key in DONE on D1 it will show 20% on K1
if user key in DONE on E1 it will show 25% on K1
if user key in DONE on F1 it will show 30% on K1
if user key in DONE on G1 it will show 45% on K1
if user key in DONE on H1 it will show 70% on K1
if user key in DONE on I1 it will show 70% on K1
if user key in DONE on J1 it will show 100% on K1
so with that I think the more elegant formula that you given won’t be able to use also right given the not so incremental percentage value. Is there any other way to solve that issue?

I try to use the Data Validation but seem like the Ignore blank check box only will allow me to work with list item only. Would really hope that you can show me how to use the Data Validation option to limit the user from skipping the cell.

Thanks again for your help, I really appreciate it.


Report •

#5
November 5, 2011 at 15:13:51
✔ Best Answer
If you needed something other than what you asked for, why didn't you ask for it? By trying to simplify the question, you ended up making more work for those that are trying to help you.

Try this formula in K1:

=CHOOSE(COUNTIF(A1:J1,"DONE")+1,"",0.5,0.1,0.15,0.2,0.25,0.3,0.45,0.7,0.7,1)

For the Data Validation try this:

1 - Select B1:J1
2 - Data...Validation...Custom
3 - =A1="DONE" (the formula will automatically update to look "left" 1 cell for each cell in the range B1:J1)
4 - Uncheck "Ignore Blank" since we don't want it to ignore blank cells
5 - Click the Error Alert tab
6 - Enter a message for your users

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#6
November 5, 2011 at 21:27:16
Thanks a million DerbyDad03. I will keep your advice in mind next time if I needed for help.

Report •

Ask Question