Question description:
This user has given permission to use the problem statement for this
blog.
I need help with subtraction following true/false criteria. For example
a1 has a value. c1 has a value, d1 has a y for yes or is left blank for no.e1 has value, f1 y or blank and so on. I need to subtract the values to the left of the column with "y" from a1 and each each other y column to get an answer into let's say k1.
a1=30,000 a3=2500. a4=y. a5=1500, a6="blank". a8=2000. a9=y.
ai-a3-a5=a8 if y to the right.
Solved by G. A. in 40 mins
This is the chat thread from the real Excelchat help session. It contains no private user
information.
Excelchat Expert
08/02/2018 - 07:21
Welcome to Got it Pro Excel!
User
08/02/2018 - 07:21
hello
Excelchat Expert
08/02/2018 - 07:22
Would you mind if you made a quick example here of the answer you are expecting?
User
08/02/2018 - 07:24
Yes
Excelchat Expert
08/02/2018 - 07:24
Thank you!
User
08/02/2018 - 07:26
The 1st value is lets say an account balance. The y is shown if paid blank if unpaid. Subtracting paid from balance to get new balance
Excelchat Expert
08/02/2018 - 07:27
OK
Excelchat Expert
08/02/2018 - 07:28
the firts 30k is an unpaid balance
Excelchat Expert
08/02/2018 - 07:28
in A1
User
08/02/2018 - 07:28
correct
User
08/02/2018 - 07:28
correct
Excelchat Expert
08/02/2018 - 07:28
C1 is a balance Paid
User
08/02/2018 - 07:28
yes
User
08/02/2018 - 07:29
e1 is unpaid i1 is paid
Excelchat Expert
08/02/2018 - 07:29
So you need to get all the ones with "y" to the right
Excelchat Expert
08/02/2018 - 07:29
?
User
08/02/2018 - 07:29
yes each one with a y subtracted fro a1
Excelchat Expert
08/02/2018 - 07:30
Ok
Excelchat Expert
08/02/2018 - 07:30
Where do need the answer?
Excelchat Expert
08/02/2018 - 07:31
You need the data with this structure?
User
08/02/2018 - 07:31
ian the a row just somewhere to the right
User
08/02/2018 - 07:31
Like L1
Excelchat Expert
08/02/2018 - 07:31
OK
Excelchat Expert
08/02/2018 - 07:32
Give me a moment please
Excelchat Expert
08/02/2018 - 07:32
I am already working
Excelchat Expert
08/02/2018 - 07:35
=A1-ArrayFormula(sum(if(C1:J1="y",B1:I1)))
Excelchat Expert
08/02/2018 - 07:35
Are you using Excel Desktop?
User
08/02/2018 - 07:36
Actually it is in MS Works database but I believe it will accept an array formula.
Excelchat Expert
08/02/2018 - 07:37
Great!
Excelchat Expert
08/02/2018 - 07:37
Can you try with this formula?
User
08/02/2018 - 07:37
give me a moment
User
08/02/2018 - 07:41
Do i need to do control shift enter?
Excelchat Expert
08/02/2018 - 07:41
Yes
User
08/02/2018 - 07:41
Remove the words array formula correct?
Excelchat Expert
08/02/2018 - 07:41
Sorry I forgot to mention it
Excelchat Expert
08/02/2018 - 07:41
Yes
Excelchat Expert
08/02/2018 - 07:42
A1-sum(if(C1:J1="y",B1:I1))
Excelchat Expert
08/02/2018 - 07:42
with CTRL+SHIFT+ENTER
Excelchat Expert
08/02/2018 - 07:46
Any luck?
User
08/02/2018 - 07:46
I am gettig a 0 result
Excelchat Expert
08/02/2018 - 07:47
The result here is correct right?
User
08/02/2018 - 07:47
yes
Excelchat Expert
08/02/2018 - 07:47
Can you copy and paste the formula you are using in MS Database
Excelchat Expert
08/02/2018 - 07:47
?
Excelchat Expert
08/02/2018 - 07:48
MS Works*
Excelchat Expert
08/02/2018 - 07:51
=A1-sumproduct((C1:J1="y"),(B1:I1))
Excelchat Expert
08/02/2018 - 07:51
Try this one without the array formula
Excelchat Expert
08/02/2018 - 07:54
Are you still there?
Excelchat Expert
08/02/2018 - 07:59
Session is about to end
This is the output file from the real Excelchat help session:
This is an example of the expert help you can get. It contains no private user
information.
