# 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 Q244Basically 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:

#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 3row 11 0 row 12 2and 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 R244That'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 