Menu
What's New
By:
Filters

Excel programming

Kimmi

Kimmi

Slayer
Joined
Sep 18, 2005
Posts
9,733
Location
Faroe Islands
Avatar Name
Kimmi Kimmi EFnet
Hey hey!


I`m in need of some assistance with excel programming.

I would like to make this statement.

Cell A1:
IF A1<7,5 THEN print "invalid value" IN B1 _or_ IF 16>A1>7,5 THEN print value of A1 IN B1 _or_ IF A1>16 THEN print "invalid value" IN B1


In plain text:
If the value that I type in A1 is less then 7,5 then I want the text in B1 to say "invalid value".
If the value that I type in A1 is 7,5 to 16 then I want that value displayed in B1.
If the value that I type in A1 is above 16 then I want the text in B1 to say "invalid value".


Ive been looking at excel help sites, but I have noe clue what Im doing :)
 
Kimmi said:
Hey hey!


I`m in need of some assistance with excel programming.

I would like to make this statement.

Cell A1:
IF A1<7,5 THEN print "invalid value" IN B1 _or_ IF 16>A1>7,5 THEN print value of A1 IN B1 _or_ IF A1>16 THEN print "invalid value" IN B1


In plain text:
If the value that I type in A1 is less then 7,5 then I want the text in B1 to say "invalid value".
If the value that I type in A1 is 7,5 to 16 then I want that value displayed in B1.
If the value that I type in A1 is above 16 then I want the text in B1 to say "invalid value".


Ive been looking at excel help sites, but I have noe clue what Im doing :)
Click to expand...


This is very easy. Let me see if I can finish this before I'm done with work.


EDIT:
Place this formula into cell B1

=IF(A1<7.5,"Invalid Value",IF(A1>16,"Invalid Value",A1))
 
put this in B1:


=IF(A1<7.5, "Invalid Value",IF(A1>16,"Invalid Value",A1))


Now, do you have any big ESi for sale at decent price?:laugh:
 
doesn't work for me, could it be couse i have a dutch version of openoffice?
 
Could be, i had to use swedish to get it working in openoffice "OM" instead of "IF"

das said:
doesn't work for me, could it be couse i have a dutch version of openoffice?
Click to expand...
 
  • Like
Reactions: das
das said:
doesn't work for me, could it be couse i have a dutch version of openoffice?
Click to expand...
I can confirm that this works in the English version of Excel 2010.
 
In Google Docs, the code for B1:
=IF(AND(A1>7.5,A1<16),A1,"invalid value")
 
Hypnotyk said:
This is very easy. Let me see if I can finish this before I'm done with work.


EDIT:
Place this formula into cell B1

=IF(A1<7.5,"Invalid Value",IF(A1>16,"Invalid Value",A1))
Click to expand...

bettlejus said:
put this in B1:


=IF(A1<7.5, "Invalid Value",IF(A1>16,"Invalid Value",A1))


Now, do you have any big ESi for sale at decent price?:laugh:
Click to expand...

This did not work, I only get script error.

I had to rewrite it little... change "," to ";" and "7.5" to "7,5"
=IF(A1<7,5;"Invalid Value";IF(A1>16;"Invalid Value";A1))

Now it works, Its just because of language settings on my laptop :)

Thanks guys.


PS there will be more.
 
Problem2:


=IF(C5>7,5;(C5-7,5);IF(C6>7,5;(C6-7,5);IF(C7>7,5;(C7-7,5);0)))
I typed this in C14,

=IF(C5>12;(C5-12);IF(C6>12;(C6-12);IF(C7>12;(C7-12);0)))
I typed this in C15,

C14 starts to count when C5 or C6 or C7 is over 7,5.
C15 starts to count when C5 or C6 or C7 is over 12.


I want C14 to stop at 4,5.

It should look something like this:

C5: 15
C6: 0
C7: 0

C14: 4,5
C15: 3
 
das said:
doesn't work for me, could it be couse i have a dutch version of openoffice?
Click to expand...

A bit offtopic - but wasn't openoffice discontinued quite a few years ago and LibreOffice took its place? :scratch2:
 
Seams like its called Apache OpenOffice now and the latest release is from 31 october 2013 so its very much alive :)

Futurama said:
A bit offtopic - but wasn't openoffice discontinued quite a few years ago and LibreOffice took its place? :scratch2:
Click to expand...
 
Kimmi said:
Problem2:


=IF(C5>7,5;(C5-7,5);IF(C6>7,5;(C6-7,5);IF(C7>7,5;(C7-7,5);0)))
I typed this in C14,

=IF(C5>12;(C5-12);IF(C6>12;(C6-12);IF(C7>12;(C7-12);0)))
I typed this in C15,

C14 starts to count when C5 or C6 or C7 is over 7,5.
C15 starts to count when C5 or C6 or C7 is over 12.


I want C14 to stop at 4,5.

It should look something like this:

C5: 15
C6: 0
C7: 0

C14: 4,5
C15: 3
Click to expand...

Change C14 to this (by adding MIN(4,5; ...) to each result:
=IF(C5>7,5;MIN(4,5;(C5-7,5));IF(C6>7,5;MIN(4,5;(C6-7,5));IF(C7>7,5;MIN(4,5;(C7-7,5));0)))

Falagor
:bandit:
 
Kimmi said:
Problem2:


=IF(C5>7,5;(C5-7,5);IF(C6>7,5;(C6-7,5);IF(C7>7,5;(C7-7,5);0)))
I typed this in C14,

=IF(C5>12;(C5-12);IF(C6>12;(C6-12);IF(C7>12;(C7-12);0)))
I typed this in C15,

C14 starts to count when C5 or C6 or C7 is over 7,5.
C15 starts to count when C5 or C6 or C7 is over 12.


I want C14 to stop at 4,5.

It should look something like this:

C5: 15
C6: 0
C7: 0

C14: 4,5
C15: 3
Click to expand...

Also... bolded part has not full specification ;).
Formula you wrote prioritises C5 over C6 over C7. This means that if both C5 an C6 are over 7,5 (or over 12 in case of C15) then result will be counted based on C5 cell.
I understand that this is what you wanted but it should require specification (for example you could want minimum value if all three cellls C5, C6, C7 are over 7,5 ant curnetly it will not be counted like that).

Falagor
:bandit:
 
Hello guys


Im trying to expand this, and making drop down boxes.

untitled.jpg



I get this, to just make a list and simple drop down.


but what Imm trying to figure out is:

I would like to make a dropdown box, that covers and area of A1 to AD16

Yes, It will be calender months. This will be the inputs.


and I have another sheet for outputs (paycheck)
This too I would like to have dropdown boxes on.
 
Use the tool Data Validation List, and be sure to mark In-cell dropdown.
Then just mark the source....that will be the list you want to show up in your dropdown menu.
And then problem solved and you can make as long dropdown lists as you want:D
 
Oki Teddy Doki said:
Use the tool Data Validation List, and be sure to mark In-cell dropdown.
Then just mark the source....that will be the list you want to show up in your dropdown menu.
And then problem solved and you can make as long dropdown lists as you want:D
Click to expand...

I can make dropdown list with data from A1 to AD16.

not only A1 -----> A16
 
I see your problem Kimmy and it looks like you will have to get all your data in one column first and then just hide that or do whatever you want with it, but if you just use autofill from your cells that will go kind of fast. And then use the validation tool it will work.....even if it is a bit more work :)
 
input1.jpg



How do I make conditions of text?

Look in screen above.

If I want to make conditions like this:

A16:

IF A3 = "LANDRIG" THEN * B3 * 15

B16:

IF A2 = "LANDRIG_ADD_FLEX" THEN * B2 * 16

I have made Listings using the excel function "ctrl+shift+f3" and datavalidating.
Dont know what its called.
 
Kimmi said:
input1.jpg



How do I make conditions of text?

Look in screen above.

If I want to make conditions like this:

A16:

IF A3 = "LANDRIG" THEN * B3 * 15

B16:

IF A2 = "LANDRIG_ADD_FLEX" THEN * B2 * 16

I have made Listings using the excel function "ctrl+shift+f3" and datavalidating.
Dont know what its called.
Click to expand...

Put this in A16
=if(a3="LANDRIG";b3*15;"")
Put this in B16
=if(a2="LANDRIG_ADD_FLEX";b2*16;"")

It will be empty if either cell does not equal LANDRIG etc. If you want a zero to show use this

Put this in A16
=if(a3="LANDRIG";b3*15;0)
Put this in B16
=if(a2="LANDRIG_ADD_FLEX";b2*16;0)

Rgds

Ace
 
Ace Flyster said:
Put this in A16
=if(a3="LANDRIG";b3*15;"")
Put this in B16
=if(a2="LANDRIG_ADD_FLEX";b2*16;"")

It will be empty if either cell does not equal LANDRIG etc. If you want a zero to show use this

Put this in A16
=if(a3="LANDRIG";b3*15;0)
Put this in B16
=if(a2="LANDRIG_ADD_FLEX";b2*16;0)

Rgds

Ace
Click to expand...

Thank you.

Is it possible to make an easier system then this.

because I have 100 variables, LANDRIG1, LANDRIG2 .... LANDRIG100.

for me not to have a code that looks like:
=if(a3="LANDRIG";b3*15;(a3="LANDRIG2";b3*15;(a3="LANDRIG3";b3*15;......(a3="LANDRIG100";b3*15;0) ??
 
Kimmi said:
Thank you.

Is it possible to make an easier system then this.

because I have 100 variables, LANDRIG1, LANDRIG2 .... LANDRIG100.

for me not to have a code that looks like:
=if(a3="LANDRIG";b3*15;(a3="LANDRIG2";b3*15;(a3="LANDRIG3";b3*15;......(a3="LANDRIG100";b3*15;0) ??
Click to expand...

No reply on this, means.... No ?
 
I'd say use VLOOKUP

VLOOKUP could very well be the greatest function in Excel there is :)
 
You must log in or register to reply here.
Back
Top