I need Excel to do the following calculation:

Microsoft Excel 2003 (full product)
September 27, 2010 at 15:19:49
Specs: Windows XP
If Q244=1 then enter 1, if Q244=1 then enter 1 in cell R244 and delete contects of Q244, if Q244=2 then enter 1 in cell S244 and delete contents of Q244, if Q244=3 then enter 1 in cell T244 and delete contents of cell Q244, if Q244=4 then enter 1 in cell U244 and delete contects of Q244

Basically I have 5 columns of data, in column Q each cell has a different number that needs to be changed to a 1 then entered in the corresponding column. Is this even possible? I've spent several hours trying to come up with a formula & just when I think I have it, I get the wrong answer! Thanks for your help.


See More: I need Excel to do the following calculation:

Report •


#1
September 27, 2010 at 15:38:24
Let's begin by stating right up front that a formula can not delete anything from any cell. A formula can only return a result. That result might be 0 or "" (nothing) but the cell will still contain the formula.

Now, as to what you are asking for, I'm confused right from the start. The first part of your very long sentence says:

If Q244=1 then enter 1, if Q244=1 then enter 1 in cell R244...

Is that a typo? It doesn't make much sense.


Report •

#2
September 27, 2010 at 16:08:08
Sorry it is a bit long. No it's not a typo. Currently the data that I need to report on is all in column Q, represented by numbers 0 through 4 (0=home, 1=small office, etc.) I am trying to create a pivot table to keep track of what our customers are buying and the only I could come up with is to move what is in column Q to the approriate column and replacing it with a #1 so I can count it in my pivot table. Basically my spreadsheet looks like this:

Col Q Col R Col S
Home(0) Sm Office(2) Enterprise(3)
row 10 3
row 11 0
row 12 2

and so on.....

So what I was thinking is I could create a formula that said "if the value in Q10=3 (or 1, or 2, etc.) then to put a 1 in cell R10 and zero out Q10"

I'm not sure if this makes sense but I really do appreciate the help.


Report •

#3
September 27, 2010 at 17:35:29
re: No it's not a typo.

You said:

If Q244=1 then enter 1, if Q244=1 then enter 1 in cell R244

That's not a typo? Why do you repeat the words "If Q244=1 then enter 1" twice?

re: "if the value in Q10=3 (or 1, or 2, etc.) then to put a 1 in cell R10 and zero out Q10"

The first part is easy:

In R10 enter:

=IF(Q10 = 3, 1, "")

However, as I said earlier, you can not use a formula to "zero out" a cell after entering a number in it.

The only way to zero out a cell that after manually entering a number in it is with VBA. However, if you delete the value in Q10, then the value in R10 will change also - unless you use VBA to also lock the value in that cell.

Why can't you use something like COUNTIF to get the values you need?


Report •

Related Solutions


Ask Question