calculations based on information from different cells

[sdc83]

hi guys, having a little trouble trying to figure this one out or even figure out if its possible. what I'm trying to do is figure out a formula to give information based on different types of information which could be added to different cells in a row. i know that sounds a little confusing but ill try and explain in below.

target cell to start with would be J6.
if a number is added to F6 i need J6 to automatically add J5 and F6 to show a total.
if a number is added to I6 i need J6 to automatically subtract I6 from J5 to show the total.
if the word "clean" is added to A6 i need J6 to only show the word "clean".

from the cells mentioned for data input (A6, I6 & F6) only one would be used per row depending on operation being tracked.

hope somebody out there can understand this and offer some help, even if to tell me its not possible.

thanks in advance.

 

[jtakw]

Hi,

If you won't have 0 (zero) values in F6 or I6, use:

=IF(A6="clean",A6,IF(F6,F6+J5,IF(I6,J5-I6,"")))

If you might have 0 values, then use:

=IF(A6="clean",A6,IF(COUNT(F6),F6+J5,IF(COUNT(I6),J5-I6,"")))

 

[sdc83]

Hi,

If you won't have 0 (zero) values in F6 or I6, use:

=IF(A6="clean",A6,IF(F6,F6+J5,IF(I6,J5-I6,"")))

If you might have 0 values, then use:

=IF(A6="clean",A6,IF(COUNT(F6),F6+J5,IF(COUNT(I6),J5-I6,"")))

hi thanks for that it works exactly how it was meant to except i just found one issue. iv been adding multiple rows of data and I've now realised that when a cell lets say J6 contains "clean" and i add a number to F7 then cell J7 reads "#VALUE!" where i would need the J cell to read the F cell value if the J cell above the target cell reads "clean".

hope this makes sense I'm starting to confuse myself here.

 

[jtakw]

Yes, I'm a bit confused, and not following.

Please post some sample data, preferably using XL2BB (see my signature), or at least in Table format.
Explain the requirements and show expected results, thank you.

 

[sdc83]

Yes, I'm a bit confused, and not following.

Please post some sample data, preferably using XL2BB (see my signature), or at least in Table format.
Explain the requirements and show expected results, thank you.

hi, sorry would need approval to download software (which i will be requesting Monday). have added a snipping to help try and explain with some random added information. as you can see the values in J cells are adding and subtracting fine depending on the data in relevant F or I cells until J cell has "clean". so if J5 automatically adds "clean" i would need the next cell J6 to take its value from F6 which in the case below would show 50.00m³. sorry for the hassle i know this is getting a little confusing but i really appreciate your help.

 

[jtakw]

Ok, this will work in this instance, but I suspect there may be Other problems...
Also, the formula can't be placed in J2 to copy down, as you'll be referencing J1, which is "ROB/Clean"

=IF(A6="clean",A6,IF(J5="clean",F6,IF(F6,F6+J5,IF(I6,J5-I6,""))))

 

[sdc83]

Ok, this will work in this instance, but I suspect there may be Other problems...
Also, the formula can't be placed in J2 to copy down, as you'll be referencing J1, which is "ROB/Clean"

=IF(A6="clean",A6,IF(J5="clean",F6,IF(F6,F6+J5,IF(I6,J5-I6,""))))

hi, thanks for that. same problems as i have been having as soon as one problem is sorted it causes another issue. really think in would be easier to continue adding the J cell information manually. it would of been nice to have a time saver but everything tried so far seems to either just straight up not work or conflicts with other important formulas on the sheet. I'm trying hard to learn excel but it can be a real nightmare at times. i don't suppose you have any ideas of good sources of excel online or software learning solutions?

 

[jtakw]

What is the new challenge/problem?

Anyway, this modified version of my formula can be placed in J2 copied down:

Book3.xlsx
	A	B	C	D	E	F	G	H	I	J
1										ROB/Clean
2	Load					100				100
3	Load					50				150
4	Discharge								150	0
5	clean									clean
6	Load					50				50
Sheet1052

Cell Formulas
Range	Formula
J2:J6	J2	=IF(A2="clean",A2,IF(COUNTIF(J1,"*clean"),F2,IF(F2,F2+J1,IF(I2,J1-I2,""))))

 

[sdc83]

What is the new challenge/problem?

Anyway, this modified version of my formula can be placed in J2 copied down:

Book3.xlsx
	A	B	C	D	E	F	G	H	I	J
1										ROB/Clean
2	Load					100				100
3	Load					50				150
4	Discharge								150	0
5	clean									clean
6	Load					50				50
Sheet1052

Cell Formulas
Range	Formula
J2:J6	J2	=IF(A2="clean",A2,IF(COUNTIF(J1,"*clean"),F2,IF(F2,F2+J1,IF(I2,J1-I2,""))))

the latest formula works fine but the latest issue is when clean is added to one of the J cells, the cell below automatically reads 0.00m³. I have multiple tabs of the same lay out for different information, all are identical except the information logged on them. and then i have a main page which shows all the most recent entry's from each tab on the one sheet. the issue is when i have a clean the 0.00m³ below stops the main page from showing clean as its reading the 0.00m³ as the most recent entry and as 0.00m³ is a viable entry its misleading.

 

[jtakw]

If you need additional help to sort this out, I'll need to know how this Sheet interacts with your other Sheets (main page), if there are formulas to collect data between these sheets, I'll need to know what those formulas are.
And, this 0.00m3, is it manually entered, from formula, and is that Actually just 0 (zero), custom formatted as 0.00m3 ?

I would think you really need to show samples in table format if you can't do XL2BB, as a last resort, you can upload a sample file to a Free file sharing site (e.g. Dropbox) and post the link here.