Quick Easy question

[jmahlow7]

If I have a function such as F4 =F2-(D2*100), since its subtracting and If I have no data in there it will always be -100 until I put data. How can I make it show nothing until data is entered?

Also Is there also an easier way to use this formula for the full column instead of dragging it down 1000 rows?

 

[mole999]

it is a formula, rather than a function

IF(F2="","",=F2-(D2*100)) will test for an empty F2

IF(D2="",""(IF(F2="","",=F2-(D2*100))) should then test for an empty D2 as well
untested

 

[Hercules1946]

Try:
=IF(F2-(D2*100), -100, "", F2-(D2*100))

If you type this into (eg) Cell A1, then right-click Cell A1 and select Copy from the drop-down menu;
Then right-click the Letter A in Col A to highlight the whole column;
Then select Paste from the drop-down menu

That will copy the formula to every cell in Column A.

 

[B___P]

A simply way

=F2-(D2*100)*(F2=D2) will put 0 instead of blanks.

Second poin: select a large range in a column and enter the formula with Ctrl+enter. All cells in range will be filled with the formula.

Bye

 

[Hercules1946]

Try:
=IF(F2-(D2*100), -100, "", F2-(D2*100))

Oops -- typo it should read:

=IF(F2-(D2*100) = -100, "", F2-(D2*100))

 

[jmahlow7]

Oops -- typo it should read:

=IF(F2-(D2*100) = -100, "", F2-(D2*100))

It Worked!!! Thanks!

 

[B___P]

I made a typo in my post: the right formula is =IF(OR(D2=0,F2=0),-100,F2-(D2*100)) .Entering with ctrl+enter after selecting a range will fill it all with the formula.

Sorry for my mistake.

 

[jmahlow7]

I made a typo in my post: the right formula is =IF(OR(D2=0,F2=0),-100,F2-(D2*100)) .Entering with ctrl+enter after selecting a range will fill it all with the formula.

Sorry for my mistake.

No Problem, also can you tell me how to automatically number column A starting from A2 only when data is entered? Row 1 has header info

 

[B___P]

a dirty solution:
A2=1
A3=IF(B3<>"",A2+1,"") then drag it down
An empty cell to the right stops numbering so you have to avoid empty rows inbetween formula/data area