Blue Flower

Free Games Gifts

How to calculate that the game gets the buyer a gift depending on the amount of goods purchased in the shopping mall

Using Excel very quickly, we can calculate that the game gets a buyer, if in market store purchases of goods for a certain amount of money.

In this Excel example tutorial I will show how in Excel we can perform the calculation that the game gets a buyer once it exceeds a certain financial amount of the invoice purchase goods at a merchant house.

In the example below you see in 'G' column limited amounts provided that the buyer received as a gift a free game. In 'H' column you notice the names of games for PC and mobile phones that department store gives free of charge if the customer exceeds the limited amount of purchased goods.

Names of free games as gifts are:

  1. Hello Kitty game
  2. Barbie game
  3. Commando 2 game

In 'A' column note the names of customers (buyers)

In 'B' column note the amount of goods purchased. These goods can be not just one product but many different products

In 'C' column note the total amount of goods purchased

In 'D' column notice games that the customer has received the gift if the transferred amount of money saved, this column is solved by using the Excel IF function.

In 'D' column notice games that the customer has received the gift if over a certain amount of money, this column is solved by using the VLOOKUP function that is nested in an Excel IF function or formula.

 

A

B

C

D

E

1

buyer

quantity

sum amount

gift (formula IF)

gift (formula Vlookup)

2

A

1

$320

   

3

B

1

$245

   

4

C

5

$630

Barbie game

Barbie game

5

D

1

$289

   

6

E

1

$540

   

7

F

1

$620

   

8

G

1

$570

   

9

H

4

$620

Hello Kitty game

Hello Kitty game

10

I

1

$320

   

11

J

1

$620

   

12

K

8

$620

Commando 2 game

Commando 2 game

13

L

2

$850

Hello Kitty game

Hello Kitty game

14

M

2

$480

   

15

N

1

$350

   

16

O

2

$120

   

17

P

8

$360

Barbie game

Barbie game

18

         

19

     

$ amount

gift

20

     

$1,000

Hello Kitty game

21

     

$2,500

Barbie game

22

     

$4,000

Commando 2 game

 

Game gift - free game

The first way of dealing with free gifts games. The formula in 'D' column solves multiple conditions and the following: Note that you have used three IF functions that are nested functions, because we have three conditions (game gift)

=IF(B2*C2>$G$4;$H$4;IF(B2*C2>$G$3;$H$3;IF(B2*C2>$G$2;$H$2;"")))

Another way of dealing with free gifts games

The formula in 'D' column. You notice that was used VLOOKUP function that is nested in IF function/formula. Cells range 'D20:E22' in this case the Define Name 'gifts'.

=IF(B2*C2<1000;"";VLOOKUP(B2*C2;gifts;2))

You can set formula in ROW2 and copy down

Please see another related tutorial: Most popular PC and Cell Phone Games