ok i use excel at the moment to record monthly income (self employed). I have several cells that i have to input a figure into, and this then needs to be multiplied by a set amount,

eg n*21.04 where n is the number that changes each month, and the 21.04 is fixed every month.

How do i get the cell to do this calculation by just typing the figure in the box? What i currently do is open the previous months figures, then click the cells i need to add data to, then change the value of ‘n’ in the function line above all the cells. Is there a way to lock the function to this cell so i can just type ‘n’ in the box and it will calculate it without having to edit the function line?

My knowledge of excel is very basic 🙁

cheers

make “n” a figure form another cell, such as A4

so the contents of the calculation cell are:

=A4*21.04

then just change the contents of A4 each month.

To make the cell A4 constant so you can drag down or copy the formula to all the cells needed, make it $A$4 not just A4, you do this by pressing F4 before the A in the formula bar. Or you can bypass using a seperate cell in the formula and do edit replace, ctrl H to get the box up, type the old number for n in the top box and the new number in the bottom box and click replace all.

From Mrs Tartanscarf

another thing to try is put your fixed multipliers in a fixed cell at top of spread sheet with a description next to it eg
vat% 20
then make the calc cell go fetch the 20 from the cell each time you need it – use a fixed reference in the calc cell eg $A$2

cost cost+vat
10 A5*($A$2/100)

then when your regular multipliers change it easy to update or do what if edit like mrs tartanscarf said 10minutes ago but i’ve been tidying up in the kitchen

Stoner – yeah i thought about that, but i was wondering if there was a way to do it without the extra cells as i dont want the actual numbers ‘n’ etc visible when i print the sheets out, just the total of the variable figure and its multiplier (which is always set).

tartanscarf – copying or edit replace doesnt help i dont think as each cell i need to add a number to has its own unique multiplier, eg

A4 – n1*21.04
A5 – n2*10.63
A6 – n3*2.34
etc.

So i just want to be able to enter n1, n2, n3 into a box and have a total pop up, and if possible i dont want to have the number n1, n2, n3 visible on the spreadsheet unless the box is selected and i’ll be able to see it in the formula bar.

are you sure your multipliers won’t change? i say that having had to spend hours getting people to check spreadsheet logics when 1 piece of data has changed!

what can do is put all the data that you don’t want to show in a few rows at top and then select those rows to hide before printing – think it is view hide or view unhide after you’ve highlighted the rows

Set the cell with the number in to have the same colour text as background. Means you can print safe in the knowledge it won’t be seen. Alternatively put it in a column off to the right and hide that column, or select the print area when you print, making sure you don’t select that column

another way that is simpler than absolute references would be

colA colB colC
10 multiplier =a2*b2

then use hide for column B

So i just want to be able to enter n1, n2, n3 into a box and have a total pop up, and if possible i dont want to have the number n1, n2, n3 visible on the spreadsheet unless the box is selected and i’ll be able to

why not use two sheets then.

one where you enter user data, the other where you have a formatted output report.

There are a few options which might do what you want reasonably easily:

(1) Put the data on a second sheet and only print the first sheet. Change the value on the second sheet and the first sheet updates. Instead of “=A4*20.4” it then becomes “=Sheet2!A4*20.4”

(2) Still put it in A4 but colour the text white [this is a really crude bodge but will work if you use this sheet often enough to remember which cell etc.

(3) Make the cell “hidden” – select the cell, go to format> cell, in number format select “custom” and then enter “;;;” without the quotes in the box on the right. The cell is then hidden. You can still enter values in it but they just don’t appear (or print – unless you force them to).

You could do fancy stuff with macros – if you really want a dialogue box to appear and “collect” the data – but its more complex than you need.

thanks for the answers!

I’ve gone for…. the crude bodge of making the text colour white 🙂

i’ll see how i go with this, theres only 11 cells that need data in and they are all sequential cells in a column so it should work ok.