Forum Discussion
Formula help in excel spreadsheet
I need help with inserting a formula into a spreadsheet I have. Let me explain the criteria.
Column "O" (PAID) uses this formula; O693=(K693-25)*N693+30. Works great! My problem ..... I have a specific use for this spreadsheet I am trying to develop. Column O has two different options to get the amount in each line. One type of number is based on the formula above using column N times the Original (K) - 25, times column N + 30. The other type of number is just a base number entered by user i.e. 25, 30, 32 etc. I need to have it so the user enters a number in column "K" and either a number in column "N" or a "base number in column "O". to determine if column "O" uses the formula or not. The user will not see the formula, but it will be changeable. The crux of the matter is: I want to give a user the form below in Excel. No formulas visible. They will enter numbers in line items in columns "K" and "N". When they enter in "N", the formula will apply to line item in column "O". Otherwise, they can just enter a number in "O". I can do this if I just do one line item at a time, but I can't get a formula to do it referring to column i.e. N:N example I've tried O=(IFNUMBER (N:N), =(K693-25)*N693+30. I know that's not right, but how can I accomplish this. I love Excel, but I am just not that good at it ....... yet (I am 80 years old....LOL). Thank you so much for any help! I am running out of time to accomplish this!
Sincerely, Mel Lange
| K | L | M | N | O |
| KILOGRAM | PAY PER KILO | PAID | ||
| ORIGINAL | MORE | TOTAL | ||
| 34.85 | 35 | $1.25 | $42.31 | |
| 34.94 | 35 | $1.25 | $42.43 | |
| 34.57 | 35 | $1.25 | $41.96 | |
| 34.67 | 35 | $1.25 | $42.09 | |
| 15.70 | 16 | $1.25 | $25.00 | |
| 43.69 | 44 | $1.25 | $53.36 | |
| 92.13 | 92 | $1.25 | $113.91 | |
| 15.70 | 16 | $25.00 | ||
| 15.70 | 16 | $25.00 | ||
| 15.70 | 16 | $25.00 | ||
| 15.70 | 16 | $25.00 | ||
| 15.70 | 16 | $25.00 | ||
| 15.70 | 16 | $25.00 | ||
| 15.70 | 16 | $25.00 | ||
| 15.70 | 16 | $25.00 | ||
| 27.40 | 15 | 42 | $1.25 | $33.00 |
| 27.24 | 15 | 42 | $1.25 | $32.80 |
| 15.70 | 16 | $25.00 | ||
| 15.70 | 16 | $25.00 | ||
| 15.70 | 16 | $25.00 | ||
| 15.70 | $25.00 | |||
| 15.70 | 16 | $25.00 | ||
| 15.70 | 16 | $25.00 | ||
| 564.99 | 30 | 579 | $776.86 |
- If i understand you correctly you want column O to either be calculated OR be user entered but that doesn't work well. I recommend instead have another column for user entry and if they enter a number in that column then col O will use that but if not then it will calculate based on K and N or it could be the other way around if there is a number in N then use K and N but if not then use that new column. and if nothing in either the new column or N then leave blank. so something like:
O=IF(N693<>"", (K693-25)*N693+30, [new column] )
or applied as a dynamic array
=IF(N6:N693<>"", (K6:K693-25)*N6:N693+30, [new column range 6:693] )- m_tarler, Thank you for your help! You were right about adding a column. The formula works fine i.e. =IF(O669<>"", (K669-25)*O669+30, N669)
I can't get they array to work but this is a large "weekly" workbook for a year's records (52 weeks)) so I have to work on that. has text at the beginning of each week.
Thanks again .......
