Archive

Posts Tagged ‘Qatar’

Learn To : In Excel – SUMIF and SUMIFS [ Part-2 SUMIFs ]

In SUMIFS we are talking about more than one conditions to apply on the same range or other range before we calculate the SUM of the target range.

Case Assumption: As in Part-1, Assume we have a table, with 5 columns (as in image-1) columns titles are [Date, Item, Quantity, Price and Total] containing our Expendituer and we have another table call it Summaries, we want some totals to be in this table. We can see the main table [Expendituer] and the Summary table as in part-1 but here we will add a year column to the Item-Quantity box, so we want to have the Quantity of an Item in given year. Image-2.

 Image-1 Image-2

So if we write 2018 in Cell I27 we Must get the Quantity of School Bags we bought in 2018. To do this we will write this formula:

SUMIFS ()

=SUMIFS(E22:E30, D22:D30,J28,C22:C30,”*”&I28)

So in Cell K28 we will write =SUMIFS(E22:E30, D22:D30,J28,C22:C30,”*”&I28)

then we can copy the formula to other cells.

Now we have the formula in all the cells we want, we may need to do a little changes to cells number or columns that may been changed during the copy. And here is a screen shot of what we must have.

🙂 Have Fun ..

Excel, VBA Codes and Formulas-2

March 20, 2020 1 comment

Learn To : In Excel – SUMIF and SUMIFS [ Part-1 SUMIF ]

Case Assumption: Assume we have a table, with 5 columns (as in image-1) columns titles are [Date, Item, Quantity, Price and Total] containing our Expendituer and we have another table call it Summaries, we want some totals to be in this table. In Image-1 we can see the main table [Expendituer] and the Summary table.

 Image-1

In the Above Expendituer Table we have some [Dummy] data, and in our Summary Table we want to calculate the SUM of each year so in the Cell J22 we shall write this formula: =SUMIF()
SUMIF will take 3 parameters
First one is the range of applying the condition.
Second one is the condition.
Third one is the range to calculate to SUM form. So in our case [Image-1]

The range of applying the condition will be C22:C28

The condition is in Cell I22 [23,24,25 … for each row and year.]

The range to calculate to SUM form will be G22:G28

So in Cell J22 we will write =SUMIF(C22:C28,”*”&I22,G22:G28)

then we can copy the formula to other cells [I23 and I24]

So now we have the Sum in each Year. See Image-2

 Image-2

Now with same formula SUMIF, we can get the Quantity of each Item we purchase, and we will write the formula in Cell J27 [as in Image-1].

So in Cell J27 we will write =SUMIF(D22:D28,I27,E22:E28) as in Image-3 then we copy the formula to Cells [J28, J29, J30 .. and so]

 Image-3

Now we have the sum of Quantity.

🙂 Have Fun ..

Learn To : In Excel – Drop Down List in a cell.

I start working on a project for a friend, he has some requests to be done on a MS-Excel, so I thought why not to write several posts about MS-Excel capabilities.

MS-Excel is a great application to be used, I will not write to praise the program, but will jump to coding. In Excel we can use dozens of built in ready to use tools and Formulas. Also MS-Excel has a very good, easy to use and learn programming language called VBA (Visual Basic for Application) which with simple code we can perform some tasks and create Menus, buttons and functions that runs in background.

Starting from this post we will use some built-in functions and VBA’s one that I am using in my Excel files.
So let’s say we have a table that has a column called “Pay-Method”, the value we save in this column always is one of three: “Cash, Card, Cheque” we don’t want the user to type it every time, but to select it from a list.

To do this we can use an static approach or a Dynamic approach.

Static Approach: if we have a specific answers that can’t be more like (Yes,No) or as our example above (Cash, Card, Cheque) or similar cases then we can use an static approach. So First Select First cell in the Table, from the menu we will select “Data” then “Data Validation” and select Data Validation.

Now,

• From the Allow box, we select List.
• Then in Source box, we need to type the items we want to appear in each cell as a drop-down list separated by a comma. Cash, Card, Cheque
• We need to check [Ignore Blank and In-cell Dropdown] Then Click OK.

Now we will have a small arrow on the first cell in our Table, to copy the List to other Cells (Beneath) hold the small DOT and Drag it down.

And Now we are DONE, we just create a Dropdown List in all “Pay-Method” Column in the table..

Dynamic Approach: If we have a list of items and this list is growing (We may add more items ti it), such as Fruit list in a grocery, then we will use the Dynamic drop-down list.
In this case I prefer to use a new Excel Tab call it “Setting”, so we will create one. On that Tab create a Table call it “Fruit” and list all the fruits you want to be appear in the drop-down cell list (In this example I will list down F1, F2, F3, F4, F5), Select the table and from Menu select “Format as Table” and just chose any format you want. Here is a screen shot.

 Here is a screen shot

Move the Mouse next to table head until you get small Arrow, and give it a name. as in here..

Now go-back to our main Table and using the same way we did in the Static Approach (from the menu we will select “Data” then “Data Validation” and select Data Validation.) AND

• From the Allow box, we select List.
• Then in Source box, we need to type the Name we select for our table “F-Table_1” for the fruit table
• We need to check [Ignore Blank and In-cell Dropdown] Then Click OK.

Done … Now if we add new Fruit to the list “F6” it will appear in our Drop-Down cell list.

🙂 Have Fun ..

Categories: excel, Learning, Lesson

Python: Cooking App P-8

Learning : Python, Data-Base, SQlite
Subject: Writing a Cooking Application

RECIPES MENU: Show Recipe During writing all functions of the application I notes that my “Show Recipe” need to be updated, so in this post we will do some changes on “def show_all_rec(opt)” function.

First, we will change the if opt ==”name” to list all the Recipes Name with ID’s and ask the user to Enter a Recipe ID to show its information.
Here is the code ..

``` # Changes on show_all_rec(opt) - if opt =="name"

if opt =="name" :
#First we will list down all Recipes Names.
c.execute("select r_id,r_name from recipes where r_id > 0")
for each_r_name in c.fetchall():
print(' ID:',each_r_name[0], ' Name:',each_r_name[1])

get_rec_id = input ('\n Enter the Recipe ID you want to read: ')
# Get the Recipe information based on ID.
c.execute("select * from recipes where r_id ={}".format(get_rec_id))
for each_basic in c.fetchall():
print('\n  ID: ',each_basic[0])
print('  Name: ',each_basic[1])
print('  Date: ',each_basic[2])
print('  Type: ',each_basic[3])
print('  Other: ',each_basic[4])

print('\n  The Recipe Ingredients: ')
# Get all Recipe Ingredients.
get_sql = '''
select
i_name

from
ingredients_list

INNER JOIN rec_ingredient ON ingredients_list.i_l_id = rec_ingredient.i_l_id
where rec_ingredient.r_id = {}

'''
c.execute(get_sql.format(each_basic[0]))

st = 1
for each in c.fetchall():
print('  ',st,': ',each[0])
st = st + 1

print('\n\n  Cooking Steps: ')
# Get the Cooking Steps.
c.execute("select * from recipes_steps where recipes_steps.r_id = {}".format(each_basic[0]))
st = 1
for each in c.fetchall():
print('\n    Step-',st,': ',each[2])
st = st + 1

print('\n ---------------------------------------------')

```

Here is the out-put screen :

 First we have the Menus, we select 1,1,1 to get “Show Recipe By Name”

 Here is the List of All Recipes and we will enter the ID of the one we want to see.

 Here is the Recipes Information.

Notes That 1. We don’t have any Ingredient (we did not add any) so in coming post we will develop the Edit function to add some Ingredient and change/Update the Recipe Information.
2. We are not using any Validations, so if we enter any dummy data for Date the system will accept it.
3. We are not using any (avoiding Error code) such as Try…except.

Python: Cooking App P-7

Learning : Python, Data-Base, SQlite
Subject: Writing a Cooking Application

RECIPES MENU: Delete Recipe To Delete a recipe we need to point on it, we will do this by it’s ID. So we can show all recipes by names, then we select the one we want to delete and enter it’s ID, the system will ask to confirm this action by typing (y,Y) then it we will call the delete function. In Delete function “def del_recipe()” we need to delete all the data regarding this ID form three DB Tables, recipes, recipes_steps, rec_ingredient and photo. Although we did not use the photo table and and we just create it for future use, but er will write its code.

Future Plan: In coming weeks we will convert this application “Cooking Application” to GUI “Graphical User Interface” application using tkinter library (Tkinter: is a Python binding to the Tk GUI toolkit) .

Coding: So to Delete a recipe we will show all Recipes and there ID’s sorted by it’s Name and ask the user to enter the ID of the Recipe he/she want to delete.

``` # Code to Delete a Recipe

def del_recipe():
os.system("clear")
print('\n ====== Delete a Recipe =====')

# Start to list down all the Recipes Name.
print("\n\n  List of ALL Recipes we have.")
c.execute("select r_id,r_name from recipes where r_id > 0")
for each_r_name in c.fetchall():
print('   ID:',each_r_name[0], 'Name:',each_r_name[1])

# Now we ask the user to Enter the Recipe ID.
rec_del = input('\n\n   Enter an Recipe ID to be Deleted: ')

# Now we ask the user to Confirm Deleting Recipe.
sure_remove = input('\n The Recipe will be DELETE and can''t be Rolled-Back. Are you sure you want to Remove it [Y,N] ')

if sure_remove in ['y','Y']:
c.execute ("Delete  from recipes_steps where r_id = {}".format(rec_del))
db_conn.commit()
c.execute ("Delete  from recipes where r_id = {}".format(rec_del))
db_conn.commit()
c.execute ("Delete  from rec_ingredient where r_id = {}".format(rec_del))
db_conn.commit()
c.execute ("Delete  from photo where r_id = {}".format(rec_del))
db_conn.commit()

elif sure_remove in ['n','N']: # If the user decide not to delete and select N
print('\n You select NOT to remove the Recipe.')
else :# If the user enter anything else than Y or N
print('\n You must select (Y or N).')

input('\n   .. One Recipe Removed ..   Press any key .. ')

```

Python: Cooking App P-3

Learning : Python, Data-Base, SQlite
Subject: Writing a Cooking Application

Over View: In last post (Part-2) we wrote the codes for Main-Menu and other sub-Menus, today in this post we will work on “Setting Menu”.
1. Create the needed Tables. [To create all tables we need in the database]
2. Drop Tables. [If any things goes wrong then we can Drop (Delete) a Table (or more) ]
3. Insert Dummy Data. [To Insert ONE Record in the tables. (Record 0)]

First thing we need to create a database and set a connection to it, also we will import the [sqlite3 and os], to do so we will write this code:

```
import sqlite3, os

# Create the data-base & name it.
db_conn = sqlite3.connect ("cooking.db")

# set the connection.
c = db_conn.cursor()

```

Now we need to write three Functions for the Setting Menu, we will start with Table creation code and here I am posting the Data-Base Tables Diagram ..

 Click to Enlarge
``` # Function to Create the Tables

def create_tables_() :    # to create tables.

sql_recipes = "CREATE TABLE if not exists recipes (r_id INTEGER PRIMARY KEY AUTOINCREMENT, r_name text, r_date text, r_type text, r_details text, r_tags text )"

sql_photo = "CREATE TABLE if not exists photo (p_id  PRIMARY KEY AUTOINCREMENT ,r_id integer , p_name text)"

sql_ingredients_list = "CREATE TABLE if not exists ingredients_list (i_l_id PRIMARY KEY AUTOINCREMENT, i_name text )"

sql_rec_ingredient ="CREATE TABLE if not exists rec_ingredient (ing_id INTEGER PRIMARY KEY AUTOINCREMENT, i_l_id integer, r_id integer)"

c.execute(sql_recipes)
db_conn.commit()

c.execute(sql_photo)
db_conn.commit()

c.execute(sql_ingredients_list)
db_conn.commit()

c.execute(sql_rec_ingredient)
db_conn.commit()

input('\n   .. Cooking  Tables created..  Press any key .. ')

```

So now if we call this function “create_tables_() “, Tables will be created. Another function in the Setting Menu is Drop a Table, as we mention above we need this function in case we need to Delete a Table and create it again, in this function first we will list all the tables in the database and ask the user to Enter the Table need to be delete. Here is the code behind this function:

``` # Drop a Table.

def drop_table():

try:
# First we will print-out the list of Tables.
c.execute("SELECT name FROM sqlite_master  WHERE type = 'table'")
db_tables =[]
for each in c.fetchall() :
for t in each :
db_tables.append(t)
print('\n  Table List .. ')
for x in range (len(db_tables)) :
print((x+1),db_tables[x])
t = int(input('\n  Enter the number next to Table you want to Drop:  '))
c.execute("DROP TABLE {}".format(db_tables[t-1]))
db_conn.commit()
print('\n ... One Table been Dropped')

except:
print('\n  No Tables with this name .. ')

input('\n  . . . Press any key  . . ')

```

Last code to write in this post is to Insert One-Record in the tables, the reason of doing this manually (Hard-Coded) is that we have some “Primary key AUTOINCREMENT” fields in our tables, and to make it AUTOINCREMENT there MUST be a number first so we will enter the ZERO record (0).

``` # Function to Insert the Zero Record

def insert_rec_0():

c.execute ("INSERT INTO recipes (r_id) VALUES(:r_id)",{"r_id":0})

c.execute ("INSERT INTO ingredients_list (i_l_id) VALUES(:i_l_id)",{"i_l_id":0})

c.execute ("INSERT INTO rec_ingredient (ing_id) VALUES(:ing_id)",{"ing_id":0})

c.execute ("INSERT INTO photo (p_id) VALUES(:p_id)",{"p_id":0})

db_conn.commit()

input('\n ...Dummy records been Inserted ....  Press any key  .. ')

```

In Next post we will write the codes for the functions in the “INGREDIENT MENU” to Add, Delete and Edit Ingredient List.

Python: Cooking App P-2

February 6, 2020 1 comment

Learning : Python, Data-Base, SQlite
Subject: Writing a Cooking Application

Over view: In Part-1 (Click to Read) we list down the Main-Menus and sub-menus of some options, here we will start writing the codes of each menu. We will start from the Main-Menu.

Now with each menu from the last post, we will see the code and the run-time screen.

2. Ingredients Manager [will have a sub menu]
4. Show Recipes.(Meals) [will have a sub menu]
5. Recipes Information.) [ number or Recipes by date, number on tags, number of Ingredients .. ]
6. Data-Base Information.
7. Setting. [will have a sub menu]
9. Exit.

``` # Main Menu Code

while True :
os.system("clear")
print(' --------------------------------------')
print('  2. Ingredients Manager')
print('  4. Show Recipes.')
print('  5. Recipes Information.')
print('  6. DataBase Information.')
print('  7. Setting')
print('  9. Exit.')

uinput = input('\n Select the required action:  ')
if uinput == '1' :
pass
elif uinput =='2' :
elif uinput =='3' :
show_recipes ()
elif uinput =='4' :
pass
elif uinput =='5' :
pass
elif uinput == '6' :
pass
elif uinput == '7' :

elif uinput == '9' :
return
else:
print('\n  You need to select from the menu..')

```

Second menu to write is “Show Recipes”, the user will use this menu to display and read the Recipes.
[ Show Recipes ]
1. Show All Recipes.
2. Show Recipes By Date.
3. Show Recipes By Ingredients.
4. Show Recipes By Tags.
5. Show Recipes By Name.
9. Exit.

``` # Show Recipes Menu Code

def show_recipes () :
while True:
os.system("clear")
print('\n ===========[ Show Recipes ]=============')
print('  --------------------------------------')
print(' 1. Show All Recipes.')
print(' 2. Show Recipes By Date. ')
print(' 3. Show Recipes By Ingredients.')
print(' 4. Show Recipes By Tags.')
print(' 5. Show Recipes By Name.')
print(' 9. Exit')
uinp= input('\n Enter your Selection: ')

if uinp == '1':
pass
elif uinp == '2':
pass
elif uinp == '3':
pass
elif uinp == '4':
pass
elif uinp == '5':
pass

elif uinp =='9':
return
else:
```

Third menu we have in our application is “Ingredient Menu”, the user will it to manage the Ingredient list.

1. Show all Ingredient.
3. Delete Ingredient.
4. Edit Ingredient.
9. Exit.

``` # Ingredient Menu Code

while True:
os.system("clear")
print('  --------------------------------------')
print(' 1. Show all Ingredient.')
print(' 3. Delete Ingredient. ')
print(' 4. Edit Ingredient')
print(' 9. Exit')
uinp= input('\n Enter your Selection: ')

if uinp == '1':
pass
elif uinp == '2':
pass
elif uinp == '3':
pass
elif uinp == '4':
pass
elif uinp =='9':
return
else:
```

Last menu we have is “Setting Menu”, from here we can create the tables and Insert a Dummy-Data.
1. Create the needed Tables.
2. Drop Tables.
3. Insert Dummy Data. (MUST be run after Tables Creation)
9. Exit.

``` # Setting Menu Code

while True:
os.system("clear")
print('  --------------------------------------')
print(' 1. Create the needed Tables.')
print(' 2. Drop Tables. ')
print(' 3. Insert Dummy Data. (MUST be run One-Time after Tables Creation)')
print(' 9. Exit')
uinp= input('\n Enter your Selection: ')

if uinp == '1':
pass
elif uinp == '2':
pass
elif uinp == '3':
pass
elif uinp =='9':
return
else:
```

Next Post: In next post we will write the codes for Setting menu so we can run it and from there we can create the Tables and Insert the Dummy Data.