# Help required on amending a excel formula

I have this formula in a workbook of which looks at data in Column E but im trying to get it to do the same but when data is put into column E OR F .

Any Ideas?

=IF(A11>0,E11-SUM(H11:V11),"")

Microsoft ExcelMicrosoft Office

Last Comment
byundt

8/22/2022 - Mon
Sam Jacobs

The formula as written is in "relative" mode, which means that if you copy the formula to another cell, all of the cell references will automatically be adjusted. So if you copy the formula to column F as is, all references will be adjusted.
=IF(B11>0,F11-SUM(I11:W11),"").

If you want any references to be absolute, you would preference the row or column (or both) with a dollar sign. For example, if you always wanted to sum H11-V11 regardless of the row or column the formula was in, you would use:
=IF(A11>0,E11-SUM(\$H\$11:\$V\$11),"").

Now if you copy the formula to column F, it would look like:
=IF(B11>0,F11-SUM(\$H\$11:\$V\$11),"").

Only the row/column references without \$ will be adjusted.
graham james

Sorry im confused there but I think I may of mislead you.  The formula is shown in column W and im trying to get it to work when data put into column E or F and calculate it in W

Tom Farrar

If Sam didn't give you an answer that works, you might want to provide an example of the data.
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
Tom Farrar

Perhaps something like this?

IF(A11>0,IF(ISBLANK(E11),F11-SUM(H11:V11),E11-SUM(H11:V11)))
graham james

Enclosed is part of the workbook that Im using.

If you put a date in column A and a cash figure in column E it copies the cash figure to column W.
Then as you input data in columns H-V it reduces the figure in column W.

I need it to do the same principal but be able to put a cash figure in column E or F and then input the data into H-V etc.

tuesday.xlsb
Sam Jacobs

Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
SOLUTION