Spreadsheets & formulas

[AtticaFish]

Anyone out there a master at setting up spread sheets with calculations using formulas? I have a project that i want to get figured out and am lacking the knowledge for some of the specific formulas. I know it can be done..... just not how to do it. Have done some searching on the net for a sample spreadsheet that i could borrow formulas from, but can't seem to find one. This is for my regular job. Wanting to put together a spreadsheet that will help give quick estimates for orders of printed shirts.

I can do the simple operations, but get confused using the IF/THEN function for a range of cells.

If anyone thinks they might be able to help, i can post up a sample of what i am trying to do. Thanks!

 

[Jig Man]

You can either post it or send a pm... Might be someone round here can figer it out......

 

[Ron Don]

Omg I Ain't volunteering! Had a whole semester that was nothing but that crap. All the "drill down" that would carry formulas from multiple spreadsheets... Never again!

 

[Kdog]

Sent a PM. Maybe I can help.

 

[AtticaFish]

Thanks for the PM Kurt. I'm going to upload it here on the board. It is the prices we use for giving people their cost, so not really any secret formula or anything. I also uploaded the XLS sheet and will put it as a link at the bottom of the post. It is originally created on Open Office and exported as XLS. Here goes:

Sheet 1: I want to use this sheet to input all the specifics for the order. That all will go into Column C. I threw in some random numbers to make it look like a simple order would look. I would want all the information calculated and have the output in Column G. This would make things very easy when our customer wants to know the difference between using different numbers of colors or quantity costs if they reach the next qty level. I understand how to get all of the output calculation EXCEPT for the print price in cell G4. That G4 cell is the only one giving me the headache.
View attachment 8


Sheet 2: This is my price sheet and where i would pull my cost info from. I want it on a separate sheet so i can change cost on the services if needed.
View attachment 9

Excel File:
View attachment 7




In the example i have on sheet one, there would be 2 print prices plus the garment price that need to be added together to get the cost of cell G4. It has a 2-color front print on 24 shirts (D19 from sheet 2, $1.85) and a 3-color back print on 24 prints (E19 from sheet 2, $2.45) and the garment price (C6 from sheet 1, $2.95) all added together.

Does that make sense???

 

[papaperch]

When I was in electronic engineering class , they started us on real basic , computer language. The real simple stuff I can still remember. Like an AND logic circuit meant that two or more switches had to be in on position to activate circuit. An OR logic circuit was only one of a series of switched had to be in on position to energize circuit.

Then they started talking about NAND which is computerize for" not an and circuit " and NOR which translated to " not an or circuit ". They lost this poor old country boy pretty fast.

Back in the day this logic was used to operate vending machines and games like shuffle board played in taverns. Someone tried explaining spread sheets to me one day. Referring to it as a bunch of simple logic. Just a tad more complicated than the logic circuits I had studied. Told him hold it right there partner. I may have studied it but I never did learn it.

 

[hookup]

Only have a minute, so I'll start .....

Your formula's will either be nested loops or you'll have an additional work book for calculations

To learn more about nested loops, hit F1 in EXCEL and type in "nested loops". You'll get a bunch of links to tutorials.)

Then you'll need to break the B18 to B21 in workbook 2 up into two columns.

First you need some error checking. Let's say you enter 8 for quantity.

Start w/ =if(C4<12,"ERR",IF ......)

Work on each cell you're trying to estimate and work in pieces, checking the answer (or errors) EXCEL has along the way.

Then once you get the errors checking done - to make sure whoever enters the data doesn't make any logic errors, start building your nested if statements.

Hollar if you get stuck

 

[Kdog]

AF,
Gonna take a bit o concentration. Will play with it after Lab club meeting. Will do it in open office as I am not sure about formulae compatibility

 

[AtticaFish]

I do think i need to show what the output for each of the print prices is next to the cell that shows how many colors are in the print. That way i can show up front what each print price is and be able to add them up over in the G column. Same with the setups, color changes, names, etc.

I have a 'work around' the array idea that might be OK and not have to use the IF/THEN thing i don't quite understand, but just not as nice and professional looking. Found the VLOOKUP (vertical lookup) function that i might be able to use. There would just have to be a lot more input cells on the front page. Is a possibility though, just going to take some imagination.

hookup - I have looked at nesting it together, but sure makes it sound like it would be a LONG string formula with a lot of room for error. I can see why you would want to check it as you go. Can't seem to wrap my head around all the variables i need. I thought there was maybe some kind of array function that simplified it, but i guess not. The VLOOKUP is close, but no cigar. At least the way i understand how it works.

Kdog - If it is too tough, don't spend too much time on it. Isn't worth it, just would make giving quick estimates back much easier. Always drives me crazy when i give someone a price and then the decide they want to take away a color, add 20 more shirts, and put names on th back of them all. I still can do it the old fashioned way, just takes time and dang it...... this is exactly what spreadsheets are MADE for!

Thanks for all the help!

 

[Kdog]

AF,
I think this is what you are shooting for.
I did not try to get fancy and did not hide a bunch of cells
It is a bit cumbersome but thru my tests it works

oooops
The type of file that you attached is not allowed. Please remove the attachment or choose a different type.

converted to excel
The type of file that you attached is not allowed. Please remove the attachment or choose a different type.

send me an email so I can send you the file

 

[jig-guy]

I just purchased all the items I hope I need to learn excel. 20 yeras ago I used Lotus 123 but don't remeber how I used it. This is interesting stuff here.
I will be paying attention to what you folks post.
Bill

 

[AtticaFish]

Wow, Thank You Kdog! I don't mind it not looking fancy..... once the formula is there then i can move things around how i want. Maybe if i can actually see the working formula it will make something click in my head. Thanks again!

My email is: [email protected]

 

[Kdog]

Wow, Thank You Kdog! I don't mind it not looking fancy..... once the formula is there then i can move things around how i want. Maybe if i can actually see the working formula it will make something click in my head. Thanks again!

My email is: [email protected]

On its way

 

[AtticaFish]

HOLY CRAP!!!! That is it! Exactly how i want it!!!!!! :notworthy:

Now to be the royal pain in the a$$ that i am...... i have one more favor on something that i forgot to mention in the beginning. :blush: I have 2 cells on sheet 1 for Additional Prints. I would like those to be calculated the same as a front print or back print. I put them there in case someone wanted to have a front print, back print, then add a sleeve print or tag print or whatever. If you don't want to do it, i will work with what i have. If i could figure out the details, i would just try to copy what you did for the others, but don't think i can.

I definitely will repay you for this! PM me your mailing address please, shirt size too and i will hook you up when i make shirts again this spring. Have wanted to do this for a long time. sweeeeeet!

:Banane09:

 

[Kdog]

Not a problem, Sent revised SS ODF version

 

[Jig Man]

Well done KDog.... :icon14:

 

[hookup]

Thx KDog - work & commuting kept me busy

Attica - the old Fortran programmer in me loves long nested decision statement. lol

 

[Kdog]

Thx KDog - work & commuting kept me busy

Attica - the old Fortran programmer in me loves long nested decision statement. lol

then you would not be happy with my approach. I took 2 sets of statements
1 for qty to choose price for that lot size
1 for pricing to select based on number of colors

more like Isis and SPSS

Still waiting on AF to see if second set of revisions filled his needs.

 

[AtticaFish]

It worked! The 2 additional print groups had gotten all set to the first column, but i was able to see the difference when comparing to the original set (front & back print) and figure out the fix. Tested it out on an order about 20 mins ago. Worked like a dream! Much thanks Kurt!!!

btw - i already made a back-up of the original and locked the calculation cells so none of that gets changed. Locked the prices also so i will have to unlock if they ever need changed.

 

[Kdog]

It worked! The 2 additional print groups had gotten all set to the first column, but i was able to see the difference when comparing to the original set (front & back print) and figure out the fix. Tested it out on an order about 20 mins ago. Worked like a dream! Much thanks Kurt!!!

btw - i already made a back-up of the original and locked the calculation cells so none of that gets changed. Locked the prices also so i will have to unlock if they ever need changed.

Dont forget, you need to protect the sheet for cell locking to work!

I thought it went awful fast adding the second set of selectors. Thats what happens when I get distracted. I did fix your off site back up copy on my pc.