anyone good at excel or similar spreadsheet programs

defstarsteve - 6-24-2008 at 02:07 PM

I can write basic functions all day long
but I need some written that I just can't seem to get straight

basically I need a function that will take 2 inputted variables that will lead to a data search and return the appropriate answer

i.e. number of shirts vs. number of colors = price/profit

u2u or email def-star@def-star.com if you can help
or mock me if not

BDx13 - 6-24-2008 at 02:43 PM

so you need to take the value of two cells, then run those numbers through a function that includes some other, external variables, is that right?

that's a little too rich for my excel blood.

you could try elance. i've used it on a few occasions recently. if you want, write it up as clear as possible and i'll post it to my account. probably find someone to do it for $20.

defstarsteve - 6-24-2008 at 02:50 PM

kind of, it's only the 2 variables

I have all of my base prices and profits set into a worksheet, all of which are dependent on number of colors in the print and the number of shirts to be printed...

so I want to be able to enter those two variables and be given the price/profit on the job...
save me from doing it manually everytime
from that I can calculate the profit per hour and such...
I have a nice quote program that I bought a few years ago but it's not capable of pulling these number correctly...

I apprecaite the offer
i was thinking somebody might be geek enough so we could work it out... without hiring an outside nerd, but it looks like I might have to go that route.

Mark Lind - 6-24-2008 at 02:53 PM

That sounds like what you'd use Access for. If anyone here knows it will be Handsome Chris. But I think you're using the wrong application for the goal.

As related, does anyone know how to transfer a number into a time? For example, Maria was on the phone for 192 seconds. If you divide that by 60 then you get 3.20 minutes. But I want it to read 3:12 which is the actual result. Anyone know how to reformat that cell?

defstarsteve - 6-24-2008 at 02:55 PM

I don't want to use an actual databse though I just wanted to do a little spreadsheet I could carry around...
make quckie quote on if need be

there should be a regular funtion to do that conversion
let me check and see what I can find

random - 6-24-2008 at 03:06 PM

Steve,

I think I get what you're asking for. I'll send a crappy example to the email you gave to see if I'm understanding correctly, and we can take it from there.

Murk - 6-24-2008 at 03:08 PM

Steve, it sounds like a detailed "if, then" formula, lemmy think about it for a bit...

Quote:

Originally posted by Mark Lind
As related, does anyone know how to transfer a number into a time? For example, Maria was on the phone for 192 seconds. If you divide that by 60 then you get 3.20 minutes. But I want it to read 3:12 which is the actual result. Anyone know how to reformat that cell?

right click on the cell, select [format cells], under category, select [time], and it will give you a list of options.

defstarsteve - 6-24-2008 at 03:12 PM

that's what I thought as well
use an if then with nested vlookup

defstarsteve - 6-24-2008 at 03:21 PM

mark
this is it
=TEXT(A1/(24*60*60), "mm:ss")
enter 192 into a1
got 3:12

Mark Lind - 6-24-2008 at 03:44 PM

Holy shit. That worked. Amazing! Thanks.

I don't really understand how it worked. I've never used the =TEXT before. What does that mean?

And by using the , "mm:ss" I know its telling it to convert to minutes and seconds. But what other options could you potentially use in the field?

Anyone know how to do formulas when a negative number is invlved?

Mark Lind - 6-24-2008 at 03:45 PM

Quote:

Originally posted by Murk
Steve, it sounds like a detailed "if, then" formula, lemmy think about it for a bit...

Calling Handsome Chris.... these formulas are his bread and butter.

random - 6-24-2008 at 03:46 PM

Steve - email sent.

Mark - What are you looking to do with negative numbers? Excel will definitely do it, but you can change the cell formatting to use a negative sign, parentheses (used in accounting most of the time), etc.

defstarsteve - 6-24-2008 at 03:49 PM

reading now
Mark google is my best friend, I can find damn near anything, and I have no idea how the text function works
but I'm glad that worked for you

I was reaching dead ends last night that is why I brought my problems here
damn live journal

Mark Lind - 6-24-2008 at 03:51 PM

Quote:

Originally posted by random
Steve - email sent.

Mark - What are you looking to do with negative numbers? Excel will definitely do it, but you can change the cell formatting to use a negative sign, parentheses (used in accounting most of the time), etc.

Basically I'm using a formula to determine productivity variances. But then I need to apply Steve's formula to that column as well. And it is freaking out over the negative signs. It's giving me a #VALUE!

Jesus.... if I had known all you guys were Excel slaves too then I probably would have just come here with my woes before and saved myself some heartburn.

defstarsteve - 6-24-2008 at 03:55 PM

we are all nerds here....

random - 6-24-2008 at 03:58 PM

I hate seeing #VALUE!

Did you type a formula in one cell, it worked, and then you tried to click & drag the formula into other cells (and you get #VALUE! in those cells)?

I kept having it happen for some other reason at one point when trying to use some built-in formulas in Excel. It would happen if I hit "Enter". I needed to hit some combination of other keys... I think it was "Ctrl + Shift + Enter" at the same time.

BDx13 - 6-24-2008 at 03:59 PM

this thread got more action than i expected and quicker, too.

i was thinking databse for steve's problem as well, but it should be doable in excel.
for mark's, i was also thinking to simply change the cell format to a time format.

well done, everyone!

defstarsteve - 6-24-2008 at 04:01 PM

worst case I will go the database route
but If I can build the excel I can use it on my phone...
quotes made easy on the spot...
screw dragging a laptop around

Murk - 6-24-2008 at 04:11 PM

Quote:

Originally posted by Mark Lind
Basically I'm using a formula to determine productivity variances. But then I need to apply Steve's formula to that column as well. And it is freaking out over the negative signs. It's giving me a #VALUE!

try this:

Quote:

Originally posted by Murk
right click on the cell, select [format cells], under category, select [time], and it will give you a list of options.

chris - 6-24-2008 at 04:27 PM

Quote:

Originally posted by defstarsteve
worst case I will go the database route
but If I can build the excel I can use it on my phone...
quotes made easy on the spot...
screw dragging a laptop around

Shouldn't be too hard, can you post a dumbed down version of the excel sheet? I'm sure you don't want your info online but if you gave the columns and some example fake data I'm sure we could whip something up for you quickly.

defstarsteve - 6-24-2008 at 04:30 PM

give me an email address and I'll send out what I have to anyone who wants to help

XHonusWagnerX - 6-24-2008 at 04:45 PM

this thread could have been in japanese and I wouldnt have understood it any less than I do right now. :drool:

DAK - 6-24-2008 at 04:58 PM

chris - 6-25-2008 at 01:18 PM

scarsboston at y ahoo dot com

defstarsteve - 6-25-2008 at 01:28 PM

email sent

random - 6-26-2008 at 12:26 AM

Steve - You get everything working for you?