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
Last Comment
byundt
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
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
Perhaps something like this?
IF(A11>0,IF(ISBLANK(E11),F11-SUM(H11:V11),E11-SUM(H11:V11)))
IF(A11>0,IF(ISBLANK(E11),F11-SUM(H11:V11),E11-SUM(H11:V11)))
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
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
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
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 questionSOLUTION
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
I probably should have posted a sample file, because your list separator is different from mine.
tuesday.xlsb
tuesday.xlsb
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
=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.