Avatar of graham  james
graham james
Flag for United Kingdom of Great Britain and Northern Ireland asked on

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

Avatar of undefined
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

ASKER
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

ASKER
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
ASKER CERTIFIED SOLUTION
Sam Jacobs

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
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.
ask a question
SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
byundt

I probably should have posted a sample file, because your list separator is different from mine.
tuesday.xlsb
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.