Help with Microsoft Excel!

[Nate The Great]

I'm trying to make a formula say the following:

If C8 is a "C" or a "CC", C9 is a "C" or "CC", C10 is a "C" or "CC" . . . etc., then give me the sum of C9*G9*F9,C10*G10*F9,C11*G11*F9 . . . etc.

Can this be done? I'm using C or CW as initials for crops. This way I only have to update the crop on the particular field and all my marketing data will spit out automatically.

I don't know much about Excel, so if you can think of a better way, please let me know.

 

[JayKeeley]

DEL *.*

 

[Russell]

I'm sure what you're asking can be done with excel - I've done some fairly in depth statistical analysis with it, and it can do a lot - but I don't actually get what your asking. If all you want is the sum of C9*G9*F9,C10*G10*F9,C11*G11*F9 in a cell, all you have to do is type

=(C9*G9*F9)+(C10*G10*F9)+(C11*G11*F9)

Into the cell and that will do it.

EDIT - but you can't do that with letters, so? If you email me the file and tell me what you want it to do I can have a go... You can use ifs and other logic tho, so I'd imagine whatever you want to do shouldn't be too hard.

Example: =IF(C8="CC",(C9*G9*F9),0) - if C8 is CC that will give you C9 times G9 times F9, else it will give you 0. Is that what you had in mind?

 

[Nate The Great]

Example: =IF(C8="CC",(C9*G9*F9),0) - if C8 is CC that will give you C9 times G9 times F9, else it will give you 0. Is that what you had in mind?

Yes! That seems correct. I'll go give it a try.

 

[Nate The Great]

Example: =IF(C8="CC",(C9*G9*F9),0) - if C8 is CC that will give you C9 times G9 times F9, else it will give you 0. Is that what you had in mind?

OK. So I'm one step further. Now I just need to make it so I can plug in any of 4 different crops: C, CC, W, or CW, in the same cell, but depending on the crop, the formula will adjust.

Example: If C8 is CC, that will give you E8 times G8 times F8, else it will give you 0, OR if C8 is C, that will give you E8 times G8 times F8, else it will give you 0, OR if C8 is W, that will give you D8 times H8 times F11, else it will give you 0, OR if C8 is CW, that will give you D8 times I8 times F11.

I've updated all cells in my example to exactly what they should be.

I will worship you, Russel, if this can be done.

 

[Russell]

=IF(C8="CC",(E8*G8*F8),IF(C8="C",(E8*G8*F8),IF(C8="W",(D8*H8*F11),IF(C8="CW",(D8*I8*F11),0))))

Should do it - I tested it and it works on my computer. If you want the formula so you can move it around and change the squares it is using it'll need to be changed a little tho. Hope that helps.

 

[Nate The Great]

=IF(C8="CC",(E8*G8*F8),IF(C8="C",(E8*G8*F8),IF(C8="W",(D8*H8*F11),IF(C8="CW",(D8*I8*F11),0))))

Should do it - I tested it and it works on my computer. If you want the formula so you can move it around and change the squares it is using it'll need to be changed a little tho. Hope that helps.

That should do it, but I would like to be able to move the formula around. I was going to try to figure that out next, but if you know a simple way please let me know.

You've saved me a lot of headaches so far.

 

[Russell]

It depends on which cells you want to keep the same when you move, and which you don't. Just put a $ before anything you don't want to change; for example $C$8 will always be C* wherever you move the formula. C$8 will always be 8, but if you move the formula across one will be D8. $C8 will always be C, but if you move the original formula up and down the numbers will change. So if you've set it right you should just be able to drag the formula up and down and it will automatically change to the right squares.

 

[Nate The Great]

Excellent. That should work. Thanks again.

 

[Nate The Great]

Russel: I emailed you the spreadsheet, I'm having a small issue.

 

[Russell]

Sorted